Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm trying to us RANKX so that I can show the top 5 or so values in my data. Somewhere along the way, I've made a mistake. The RANKX function is only returning "1" for every single entry. At first, I thought this was a "filter context" issue so I wrapped the "table" value in an ALL function. No change.
This is the current form of my code:
rankImpressions = RANKX( ALL('Weekly Summary'), SUM('Weekly Summary'[Impressions]), , ASC )
I read on a post here that my code could be incorrect because of the filter context of the actual expression--AKA the "SUM('Weekly Summary'[Impressions])"--so I need to wrap the expression value in a CALCULATE function. So this is what I tried to do:
rankImpressions = RANKX( ALL('Weekly Summary'), CALCULATE( SUM('Weekly Summary'[Impressions]), ALL('Weekly Summary'[Impressions])), , ASC )
This also did not work (rank was still returning all "1").
Does anyone know how my snippets of code are incorrect?
Solved! Go to Solution.
CALCULATE is needed to perform the context transition for each row of the table
otherwise the filter context is always the same and all items will rank 1
rankImpressions = RANKX( ALL('Weekly Summary'), CALCULATE ( SUM('Weekly Summary'[Impressions]) ), , ASC )
However if you already have a Measure for SUM( 'Weekly Summary'[Impressions] ) then you don't need the CALCULATE
rankImpressions = RANKX( ALL('Weekly Summary'), [MEASURE], , ASC )
Hope this helps!
CALCULATE is needed to perform the context transition for each row of the table
otherwise the filter context is always the same and all items will rank 1
rankImpressions = RANKX( ALL('Weekly Summary'), CALCULATE ( SUM('Weekly Summary'[Impressions]) ), , ASC )
However if you already have a Measure for SUM( 'Weekly Summary'[Impressions] ) then you don't need the CALCULATE
rankImpressions = RANKX( ALL('Weekly Summary'), [MEASURE], , ASC )
Hope this helps!
Thanks @Sean! You're quickly becoming one of my favorite people on these forums!
I have a similar problem and can't seem to find whats going wrong! The RankX function gives two distinct ranks to the rows viz. 1 and the highest rownumber (eg. 20,000)
Here is what my formula looks like:
Rank = RANKX (all(TableName),[All_Assets]) --- gives rank = 1 to rows with All_assets > 0 and rank =maxRank (something like 20,000) to rows with All_assets = 0
I also tried: Rank = RANKX(all(TableName),calculate([All_Assets])) -- gives me the same result. And anyway I don't think I need to include Calculate since " All_assets" is a measure which is precalculated.
All_Assets is a measure which is calculated as follows:
All_Assets = DISTINCTCOUNT(TableName[AssetName])
Any idea, what is going on?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |