topic Re: DistinctCount OF Ranks in DAX Commands and Tips

DistinctCount OF Ranks

Amrselim1989 — Sat, 08 Jul 2023 06:10:12 GMT

Hello Guys,

kindly note i have the below code which is working fine and gives me the rank i want..

Rank = VAR SourceTable = ADDCOLUMNS ( ALL ( Sales[ItemCode] ), "@Amt", [Sum of Net Sales Value] ) 
VAR Result = RANK ( DENSE, SourceTable, ORDERBY ( [@Amt], DESC) )  
RETURN Result

but what i want to achieve is to distinct_count of the ranks itself, so i was thinking to create a variable table inside the measure, and then use distinct count to count the ranks something like this,

 Rank = ADDCOLUMNS ( ALL ( Sales[ItemCode] ), "@Amt", [Sum of Net Sales Value] )  
VAR Result = RANK ( DENSE, SourceTable, ORDERBY ( [@Amt], DESC) ) 
VAR _DisCount = ADDCOLUMNS(ALL ( Sales[ItemCode] ),"@Rank",Result) 
VAR _Dis_Count = distinctcount(_DisCount,[@Rank]) 
RETURN Result & "of" _Dis_Count

so i can say that product is rank 2 of 25 total ranks,

i want it in the same measure not with a separated table outside, is it possible, i don't know why i can't use this variable column [Rank] like this ??!!!!

Re: DistinctCount OF Ranks

Jihwan_Kim — Sat, 08 Jul 2023 07:44:18 GMT

Hi, I am not sure what is the expected result, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.

Distinct count of rank = VAR _SourceTable = ADDCOLUMNS ( ALL ( Sales[ItemCode] ), "@Amt", [Sum of Net Sales Value] ) VAR _currentrowrank = RANK ( DENSE, _SourceTable, ORDERBY ( [@Amt], DESC ) ) VAR _DisCount = FILTER ( ADDCOLUMNS ( ALL ( Sales[ItemCode] ), "@Rank", RANK ( DENSE, _SourceTable, ORDERBY ( [@Amt], DESC ) ) ), [@Rank] = _currentrowrank ) VAR _Dis_Count = COUNTROWS ( _DisCount ) RETURN _Dis_Count

Re: DistinctCount OF Ranks

Amrselim1989 — Sat, 08 Jul 2023 13:31:25 GMT

Hello sir,

thank you so much to reply me, but what i want to achieve is not that, it's actually so simple

it's so easy to achieve with the calculated column, but i want it to be inside the measure, to follow other filtrations that may occur, is it possible??!!!

why i can't use this variable column i did just as i did it up there this is sooo frustrating!!!

Re: DistinctCount OF Ranks

Jihwan_Kim — Sat, 08 Jul 2023 19:09:08 GMT

Hi,

Please check the below picture and the attached pbix file.

Distinct count of rank V2 = VAR _SourceTable = ADDCOLUMNS ( ALL ( Sales[ItemCode] ), "@Amt", [Sum of Net Sales Value] ) VAR _currentrowrank = RANK ( DENSE, _SourceTable, ORDERBY ( [@Amt], DESC ) ) VAR _t = SUMMARIZE ( ADDCOLUMNS ( ALL ( Sales[ItemCode] ), "@Rank", RANK ( DENSE, _SourceTable, ORDERBY ( [@Amt], DESC ) ) ), [@Rank] ) VAR _Dis_Count = COUNTROWS ( _t ) RETURN _Dis_Count

Another way to achieve this is, I think, try to get the last rank number in the rank result.

Re: DistinctCount OF Ranks

Amrselim1989 — Sun, 09 Jul 2023 00:37:35 GMT

Yes, Thank you so much, i just knew that i have to use the X aggregation function inside the variable tables to be able to use their column like MAXX instead of the distinct count...