Unexpected results with VAR in ranking calculation (RANKX, CALCULATE, FILTER)

[datadonuts]

I am working on a report about olympic history ... medals and ranking, that stuff (something, probably every beginner did).

The question I tried to solve: what was the best result of a country (selected by a slicer), returning the games (year) and the count of medals total. See below screenshots. That's faily easy in a table, but I want to use it in a card. I came up with three measures:

1. calculate the count of medals

Best year medal count =
VAR Medalgroup =
GROUPBY (
Medals,
Medals[Games],
Medals[Country],
"Total Medals", COUNTX ( CURRENTGROUP (), Medals[Medal] )
)
RETURN
MAXX ( Medalgroup, [Total medals count] )

2. get the rank by games

Rank Medals Games =

RANKX (ALL ( Medals[Games]),[Total medals count],,DESC)

3. Return the games (year) that country had the best result

Best Result Games =
CALCULATE (
FIRSTNONBLANK ( Medals[Games], 1 ),
FILTER ( ALL ( Medals[Games] ), [Rank Medals Games] = 1 )
)

So far so good. Got the correct results (yeah!)

To reduce the number of measures I had the crazy idea to combined measure 2 + 3 into one using VAR; surpridingly, it gives me a wrong result!

Best Result Countries & Games WRONG =
VAR rankgames =
RANKX ( ALL ( Medals[Games] ), [Total medals count],, DESC )
RETURN
CALCULATE (
FIRSTNONBLANK ( Medals[Games], 1 ),
FILTER ( ALL ( Medals[Games] ), rankgames = 1 )
)

I scanned through the forums and found some articles about strange behavior of VAR, but I couldn't clearly catch it, so please allow me to post another strange VAR behavior question here.

Wait! I am not ready yet: 

I also tried to solve the same question with SUMMARIZE plus RANKX or TOPN, bc that seemed quite obvious to me.

But SUMMARIZE still gives me headaches (literaly), and I couldn't find a solution that way.

If someone has an elegant solution using SUMMARIZE, that's a real winner!

Thanks to all. 

[PaulDBrown]

@datadonuts 

I have come across this repeatedly and with other functions too. One possible solution is to wrap the VARs in a CALCULATE fuction, but in my experience this is more "miss"  than "hit": in other words, it doesn't always (if rarely) solve the (my) problem. 
There is undoutedly a complex explanation which is way beyond my scope of understanding or knowledge of the intricacies and subtleties of DAX. 
I just resort to writing separate measures: if the result looks weird, I try seperate measures "et voilà...".

Sorry, not very helpful, I know. But I've given up tearing my hair out when VARs "don't deliver". The real risk is actually not "seeing" that the result is not what you are expecting to be able to act accordingly.

[Greg_Deckler]

@datadonuts See if this helps: https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

If not, @ me and post sample data as text and expected output. Your combined measure won't work, in fact not entirely sure what RANKX returns in that kind of a statement.

[datadonuts]

Thanks for the link. I will definetly dive into it. Hope I can get a grap on it 😉

[v-deddai1-msft · Accepted solution]

Hi @datadonuts ,

You can use the following measure to combine 2+3:

Best Result Countries & Games WRONG =
VAR A =
    ADDCOLUMNS (
        SUMMARIZE ( 'Medals', 'Medals'[Games] ),
        "rankcolumn", RANKX ( ALL ( 'Medals'[Games] ), [Total medals count],, DESC )
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( 'Medals'[Games], 1 ),
        FILTER ( A, [rankcolumn] = 1 )
    )

And you can refer to my sample pbix file:  https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EV-N40M6oClDipcsQ5...

According to my knowledge, my interpretation of your issue is that when you use RANKX as the VAR parameter, its value has been calculated before you perform the CALCULATE calculation, and it will not be calculated again in the context of the Filter row, so Produces the wrong result.

When you put RANKX as a measure into the CALCULATE calculation, the value of rankx will be calculated in the filter row context loop.

You can refer to the  Pseudo Logic for measure using RANKX in https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures:

1. For any data point on a visual the calculated measure needs to produce a value for, execute the calculated measure (the visual in our example will call the calculation 4 times in total – in any order – in parallel).
2. The RANKX function will execute the <expression> using the current filter context and store the result internally.  This should be the same value as if the <expression> was used in its own calculated measure on the visual.
3. The RANKX function then runs as many loops as there are rows in the <table> used as the first parameter.
   1. For each internal loop of the RANKX function, the <expression> will be executed using the current filter context for that loop.
   2. the result of the <expression> will be added to an internal list
4. Once the loops are finished, the list that was constructed at step 3.2 will be sorted
5. The value stored at Step 2 will be used to search the sorted list from Step 4 and return the position within the list.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai