DistinctCount OF Ranks

[Amrselim1989]

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 ??!!!!

[Jihwan_Kim]

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

[Amrselim1989]

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

[Jihwan_Kim · Accepted solution]

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.

[Amrselim1989]

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...