Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Using RankX in DAX For PowerPivot / PowerBI

A common reporting need is to rank a certain Attribute by a value.  In DAX, the syntax is the following: RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])   As always, you can also r...
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Anonymous Can you please show the correct way to write the RANKX function when needing to use the CALCULATE function? You showed that using something like this:

IF (
    HASONEVALUE ( DimProduct[Manufacturer] ),
    RANKX ( ALL ( DimProduct ), SUM ( [TotalCost] ) )
)

is incorrect, but I didn't see anywhere where you showed the correct way of writing that formula. This happens to be something that I'm currently trying to do and I have yet to find anyone who's shown how to correctly write this formula (myself included). An example would be tremendously appreciated.

 

In case it'd be helpful, here is the current form of my code:

rankImpressions = 
RANKX(
	ALL('Weekly Summary'),
	SUM('Weekly Summary'[Impressions]),
	,,
	ASC
)

When you throw the result into a table, all of the Rank Values return as "1". What is it that I need to do to fix this?

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    Anonymous

    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! :smileyhappy: