topic Re: Why a calculated table can not be ranked with RANKX()? The result is 1 for every rows! in DAX Commands and Tips

Why a calculated table can not be ranked with RANKX()? The result is 1 for every rows!

Yiyi — Tue, 01 Aug 2023 11:06:23 GMT

Hello friend,

Could you kindly help me to figure out how to rank a calculated table?

I have one table as my original table and based on the original table, I have created a new table to count unique values for each media. I have also added a rank for the new table. The DAX is as follows:

SummarizedTable = ADDCOLUMNS( SUMMARIZE( ALLSELECTED(Data), Data[Media], "Unique_Item_Count", DISTINCTCOUNT(Data[HeadlineID]) ), "Rank", RANKX(ALLSELECTED(Data[Media]), [Unique_Item_Count], , DESC,Dense) )

The result I got is as follows:

I really appreciate if you could help! Thank you.

Re: Why a calculated table can not be ranked with RANKX()? The result is 1 for every rows!

eliasayyy — Tue, 01 Aug 2023 11:26:55 GMT

please try

SummarizedTable = VAR _t = SUMMARIZE( ALLSELECTED('Table'), 'Table'[Media], "Unique_Item_Count", SUM('Table'[Count]) ) RETURN ADDCOLUMNS( _t, "Rank", RANKX(_t,[Unique_Item_Count],,DESC,Dense) )

Re: Why a calculated table can not be ranked with RANKX()? The result is 1 for every rows!

Yiyi — Tue, 01 Aug 2023 12:24:16 GMT

works perfectly, you are like a magician 😉 Thanks a lot 😊

Re: Why a calculated table can not be ranked with RANKX()? The result is 1 for every rows!

Yiyi — Tue, 01 Aug 2023 15:51:43 GMT

Hello thanks for your help again! I am trying to get the ranks for the running total calculation thus I need the ranks to be distinct number. I found somewhere saying that by using RAND() might help to achieve such result. Do you know how to do it? Thanks a millions time!

Re: Why a calculated table can not be ranked with RANKX()? The result is 1 for every rows!

Yiyi — Tue, 01 Aug 2023 15:52:13 GMT

SummarizedTable = VAR _t= SUMMARIZE( ALLSELECTED('Main Data'), 'Main Data'[Media], "Unique_Item_Count", DISTINCTCOUNT('Main Data'[HeadlineID]) ) RETURN RANKX(_t,[Unique_Item_Count]+RAND(),,DESC,Dense)

Re: Why a calculated table can not be ranked with RANKX()? The result is 1 for every rows!

eliasayyy — Tue, 01 Aug 2023 15:58:04 GMT

If you are having trouble with dense and having same rank , it is true you can use rand() which will add a decimal to the total value so [unique measure] + rand() should lpwer the change to get same rank