Forum Discussion

sandee's avatar
sandee
Helper I
5 years ago
Solved

RANX Function

Hi All,    this might be a very silly asking this question On RANK DAX, however i am new and learning POWER Bi. please if you could you help me.    I have a table and i want to give "Ranking" on ...
  • AntrikshSharma's avatar
    AntrikshSharma
    5 years ago

    sandee Is this how you want it? I have attached the file below my signature.

    Measure =
VAR Yes =
    CALCULATETABLE ( Sandee, Sandee[Debt Overdue] = "yes", ALL ( Sandee ) )
VAR No =
    CALCULATETABLE ( Sandee, Sandee[Debt Overdue] = "no", ALL ( Sandee ) )
VAR CurrentSelection =
    SELECTEDVALUE ( Sandee[Debt Overdue] )
VAR Ranking =
    IF (
        CurrentSelection = "yes",
        RANKX ( Yes, [Total Due],, DESC ),
        RANKX ( No, [Total Due],, DESC )
    )
VAR Result =
    IF ( ISINSCOPE ( Sandee[Debt Overdue] ), Ranking )
RETURN
    Result

     

sandee's avatar
sandee
Helper I
5 years ago

Hi Amit,

 

Many thanks for your response, here is what i am getting after applying:

sorry for late response, this is because of poor internet connection. please can you advise further as how we can get proper ranking starting from 1 to the end of clients ?

amitchandak's avatar
amitchandak
Super User
5 years ago

sandee , I think this same case, what I faced

 

City Rank = RANKX(all(Geography[City]),[Sales]) // DO not work with City ID
Geography Rank = RANKX(all(Geography),[Sales]) //  work with City ID
City and ID Rank = RANKX(all(Geography[City],Geography[City Id]),[Sales])//  work with City ID

 

Try you Rank on both Cust Id and Cust name 

Rank = RANKX(all(Table[Cust ID],Geography[Cust Name]),Calculate(Sum('Working Ledger'[Amount Due])))

 

Basically rank is inside the other group by selected in the context 

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    5 years ago

    sandee  File is attached below my signature. You also need to work on your data model, many to many relationships is not a good thing.

    Measure 2 =
VAR vTableWithout_DebtOverdueColumn =
    CALCULATETABLE (
        SUMMARIZE (
            'Working ledger',
            'Working ledger'[Cust Name],
            'Working ledger'[Cust. No.]
        ),
        ALLSELECTED ( 'Working ledger' )
    )
VAR vTableWith_DebtOverdueColumn =
    CALCULATETABLE (
        SUMMARIZE (
            'Working ledger',
            'Working ledger'[Cust Name],
            'Working ledger'[Cust. No.],
            'Working ledger'[Debt overdue]
        ),
        ALLSELECTED ( 'Working ledger' )
    )
VAR FullRanking =
    RANKX ( vTableWith_DebtOverdueColumn, [Amount Due],, DESC, SKIP )
VAR PartialRanking =
    RANKX ( vTableWithout_DebtOverdueColumn, [Amount Due],, DESC, SKIP )
VAR DebtOverDueColumnIncluded =
    ISINSCOPE ( 'Working ledger'[Debt overdue] )
VAR HideRankFromGrandTotal =
    ISINSCOPE ( 'Working ledger'[Cust Name] )
        || ISINSCOPE ( 'Working ledger'[Cust. No.] )
VAR Result =
    IF (
        HideRankFromGrandTotal,
        IF ( DebtOverDueColumnIncluded, FullRanking, PartialRanking )
    )
RETURN
    Result