Home Forums Power Pivot measures as filters? ARGH!!!

This topic contains 11 replies, has 3 voices, and was last updated by  eisayev 6 months, 1 week ago.

Viewing 12 posts - 1 through 12 (of 12 total)
  • Author
    Posts
  • #9275

    dircur
    Participant
    • Started: 14
    • Replies: 31
    • Total: 45

    I’m embarrassed for how simple this problem is but it hits my excel brain just exactly where it isn’t getting the contextual differences as to why this doesn’t work.
    What should this formula look like?
    I have a table with classifications, Values and Start dates.

    Cat     StartDt     Rate
    A       1/1/2017    2.3
    B       1/5/2017   934
    C       1/2/2017   6.3
    D      2/15/2017    32.5
    A       10/15/2017  5.5
    B       11/24/2017   834
    C       8/16/2017    9.4

    I would like the following

    Cat                     MAx Date          CurrentRate         SD Rate
    A                         10/15/2017               5.5                    1.6
    B                         11/24/2017               834                  50
    C                         8/16/2017                 9.4                    1.55
    D                         2/15/2017                32.5                   0

    I cant seem to build a measure for the current rate.
    I keep trying to use the Max Date measure as a filter and power pivot hates me
    Max Date = MAX([StartDt])

    Current Rate = Calculate (Average([Rate]), tbl[StartDT] = [Max Date])
    PP ERROR**** you are using a bolean…

    Current Rate = Calculate (Average([Rate]), tbl[StartDT] = Date(2017,8,16))
    PP 🙂 calculates Cat C as I would expect (zeros for the rest as I would expect)

    Current Rate = Calculate (Average([Rate]), Filter(tbl,tbl[StartDT] = [Max Date]))
    PP goes through the motion but is picking up both values for the average, I only used average because I need an aggregation but I expect it to only include the 1 filtered value.

    #9276

    dircur
    Participant
    • Started: 14
    • Replies: 31
    • Total: 45

    And now
    Current Rate = Calculate (Average([Rate]), Filter(tbl,tbl[StartDT] = MAX([StartDt])))

    Appears to work.. I just cant understand why?

    #9277

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    John,

    Glad to see you continue to experiment with Power Pivot and DAX.

    You may already know that measures are automatically wrapped inside of a CALCULATE by the DAX engine (if you didn’t, now you do!)

    Here is why that is important: CALCULATE, when used inside of an iterator (like FILTER, SUMX, MAXX, AVERAGEX, etc.) can invoke a special power called Context Transition. That’s a big phrase for a simple idea: all the values in the row currently scanned are turned into a filter. I know its a headache for you now, but eventually you will get to know its power and usefulness.

    Here is my attempt to give the “why”:

    Since [MAX Date] is a measure (and measures are automatically wrapped inside of a CALCULATE by the DAX engine), and since [MAX Date] is called inside of an iterator (FILTER), context transition will occur on each row. Since your table does not have any duplicate rows, the filter created by context transition includes only on one row (itself). Since the maximum StartDt in any one row will always be equal to the StartDt in the same row, the FILTER expression will always test positive.

    In contrast, Filter(tbl,tbl[StartDT] = MAX([StartDt])) does not use a measure and so context transition does not occur and MAX is applied against all of the rows in the category and returns the value in the FILTER expression you expected.

    Clear as mud, right?

    Just for conversation, you could create a filter expression like Filter(tbl,tbl[StartDT] = CALCULATE ( MAX([StartDt]))) which would also invoke context transition and would also deliver undesired results.

    If you would like to know an example where context transition would be useful (and maybe even could “save the day”), just let me know.

    Tom

    #9351

    dircur
    Participant
    • Started: 14
    • Replies: 31
    • Total: 45

    Thanks Tom,

    In that last sentence you read my mind. If you can show an example of when I would want the Transition I would really appreciate it. If I understand why I would want this feature I think I will better understand the differences.

    #9352

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    John,

    OK. This will be a two-reply response.

    This reply will be a case of how context transition can “save your bacon” in a given scenario. The next reply will be an instance how context transition can be helpful.

    Here goes: let’s say your boss wants to see the average customer amount, so you create a measure Avg Cust Amt := AVERAGE ( Tbl[Amount] ) and build a pivot table with customer name on rows and slide your measure in the values drop zone.

    Your boss looks at the results, smiles for your effort, and then says, “That’s the average customer transaction amount, not the average customer amount. I want to see only one value and that will be average of what each customer spent.”

    So you revise your measure to AVERAGEX ( VALUES ( Tbl[CustomerID] ), SUM ( Tbl[Amount] ) ) and remove customer names from pivot rows so you just display the singleton measure. You stare at the result and think, “I know that’s not right!” and just then you get an email from your boss with the text “I’m waiting…”.

    All of a sudden you remember the saving concept: Context transition! AVERAGEX ( VALUES ( Tbl[CustomerID] ), CALCULATE ( SUM ( Tbl[Amount] ) ) ).

    You run into your boss, he/she smiles approvingly at the result, and says “Now the next thing I want you to do…” and so the story goes.

    And why did it work? AVERAGEX, as it iterates over each customer ID, invokes CALCULATE ( SUM ( Tbl[Amount] ) ) and the since you have a CALCULATE, that converts VALUES ( Tbl[CusomerID] ) into a filter, summing up only the amounts for the currently iterated customerID within the current pivot context. Restated from the point of view of the measure that did not work, without the CALCULATE, just using SUM ( Tbl[Amount] ) does not apply a filter on an iterated CustomerID and so adds up all of the amounts for all of the customers in the current pivot context.

    Please see attached workbook.

    If it still seems a little fuzzy, the next example will help solidify things.

    Tom

    Attachments:
    You must be logged in to view attached files.
    #9354

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    It is sometimes the case when a model consists of multiple tables, for example, Customers and Sales.

    If users want the “absolute” total sales for each customer, and they only want the value to be calculated one-time (when the source data from the model is refreshed), a calculated column that invokes context transition is the way to go.

    So (assuming a relationship between Customers and Sales exists) adding this formula for a calculated column in the Customers table will work:

    = CALCULATE ( SUM ( Sales[Amount] ) )

    However, if your calculated column consisted of just:

    = SUM ( Sales[Amount] )

    The calculated column would contain all of the amounts for all of the rows in the sales table.

    Another way to successfully formulate the desired calculated column: if you had a measure to evaluate sales transactions such as:

    Total Sales := SUM ( Sales[Amount] )

    Since all measures are automatically wrapped with a CALCULATE by the DAX engine, you could replace first formula for the calculated column with just:

    = [Total Sales]

    The implicit CALCULATE that the DAX engine will wrap around the measure and invoke context transition and apply a filter on each row of the Customers table, returning only the sum of related amounts.

    Cool, huh?

    #9387

    dircur
    Participant
    • Started: 14
    • Replies: 31
    • Total: 45

    Thanks Tom, sorry for the late reply.
    I didn’t get a notice when you replied. The fog is slowly lifting I will probably need to re-read this and play with the workbook a bit.

    #9389

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    John,

    Thanks for following up! Feel free to ask any questions.

    TOm

    #9424

    eisayev
    Participant
    • Started: 1
    • Replies: 7
    • Total: 8

    Hi
    Max Date=IF(HASONEVALUE(Table1[Cat]);MAX(Table1[StartDt]))

    Current Rate=CALCULATE(SUM(Table1[Rate]);FILTER(Table1;[StartDt]=MAXX(Table1;Table1[StartDt])))

    SD Rate

    First =CALCULATE(SUM([Rate]);FILTER(Table1;Table1[StartDt]=MINX(Table1;Table1[StartDt])))
    Last =CALCULATE(SUM([Rate]);FILTER(Table1;[StartDt]=MAXX(Table1;Table1[StartDt])))
    SD Rate =ABS([First]-[LAST])/2
    Attachments:
    You must be logged in to view attached files.
    #9433

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    eisayev,

    Very nice DAX code! You are doing exceptionally well with DAX/Power Pivot/Power BI.

    My response, whether right or wrong, was given to explain why one formula worked and another did not (the reason was context transition, which is a key to many advanced features in DAX).

    Maybe all John really wanted was a formula that works and I went too far off on a topic. If so, my apologies for the confusion my answer produced.

    It is great when to exchange ideas in the forums!

    Best regards,

    Tom

    #9434

    dircur
    Participant
    • Started: 14
    • Replies: 31
    • Total: 45

    Indeed Tom I was only looking for the explanation. Unfortunately dont do enough of this to reinforce the concepts.

    ie context transition feels like I am in the movie Inception and cant figure out what level of dream sequence I have hit. Am I in a dream of a dream or am I actually awake.

    Not sure if anyone else has used that reference before but it seems fitting in this “context”.   (ROFL – I’m so funny)

    #9435

    eisayev
    Participant
    • Started: 1
    • Replies: 7
    • Total: 8

    Tomallan,

    it is really very nice to hear positive comments from you to my solutions. IT IS A BIG MOTIVATION FOR ME.

    My experience with power pivot is not more than 2-3 weeks and has not already finished the book I am reading. But your solutions has helped me a lot to understand formulas.

    Thank you again for your support

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic.