topic Re: RankX in DAX Commands and Tips

RankX

Cyriackpazhe — Wed, 12 Feb 2025 11:13:33 GMT

Why am i getting the output like this

Re: RankX

bhanu_gautam — Wed, 12 Feb 2025 11:21:23 GMT

The issue you're encountering with the RANKX function in Power BI is likely due to the way the function is being used in conjunction with the ALL function. The ALL function removes all filters from the specified columns or tables, which can cause the RANKX function to rank all values as 1 because it is not considering any context for the ranking.

To fix this, you need to ensure that the RANKX function is applied in a context that allows it to rank the values correctly.

DAX
RankX = RANKX(
ALL('Calendar'[Date], 'Calendar'[Quarter]),
[Total],
,
DESC,
DENSE
)

Re: RankX

Cyriackpazhe — Wed, 12 Feb 2025 11:29:44 GMT

Also if we do as you mentioned, won't the virtual table contain all possible combinations of year and quarter. The rank would be assigned as per its position in this entire set right

Re: RankX

Anonymous — Thu, 13 Feb 2025 09:12:17 GMT

Hi Cyriackpazhe , hello bhanu_gautam, thank you for your prompt reply!

Please try the following measure:

MeasureTotal = SUM('Table'[Total])

RankX = IF ( ISINSCOPE ( 'Table'[Date].[Quarter] ), RANKX ( FILTER ( ALL('Table') , YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) ) ),[MeasureTotal],,DESC , DENSE ), IF ( ISINSCOPE ( 'Table'[Date].[Year] ), RANKX ( ALL ( 'Table' ), CALCULATE ( SUM ( 'Table'[Total] ), ALLEXCEPT ( 'Table', 'Table'[Date].[Year] ) ), [MeasureTotal], DESC, DENSE ), BLANK () ) )

Result:

Best regards,

Joyce

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

Re: RankX

Cyriackpazhe — Thu, 13 Feb 2025 09:58:53 GMT

but why isn't the meausre i wrote working. It should be correct right

Re: RankX

techies — Thu, 13 Feb 2025 11:43:46 GMT

pls try this -

Measure =

VAR totalamount = [Enrollment Amount]

VAR QuarterRank =

IF (

ISINSCOPE ( 'DATE'[quarter]),

RANKX (

ALLSELECTED ( 'DATE'[quarter] ),

[Enrollment Amount],

DESC,

DENSE

)

VAR Result =

IF (

NOT ISBLANK ( totalamount ),

QuarterRank

)

RETURN

Result

Re: RankX

Anonymous — Fri, 14 Feb 2025 06:39:01 GMT

Hi Cyriackpazhe , thank you for your feedback.

If possible, please update the sample pbix file without any sensitive information for further troubleshooting.