RANX Function

[sandee]

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 the basis of amount that client due,  

within the table there is column at the end called "Debt Overdue" which represent "Yes" and "No" if the amount is due then "Yes"

and if not "No".

"

so i trying to prepare the ranking on both the condition, if i select "Yes" then should get the ranking, if select "NO" then also should reflect the ranking. please if you could help and advise .

i tried applying this on excel using countif .

[amitchandak]

@sandee ,

if(selectedvalue(slicer[slicer]) ="no",
rankx(all(table[Cust no]), calculate(sum(Table[amount]), filter(Table,Table[Debt Overdue] ="no"))),
rankx(all(table[Cust no]), calculate(sum(Table[amount]), filter(Table,Table[Debt Overdue] ="yes")))
)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[sandee]

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 ?

[AntrikshSharma · Accepted solution]

@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

[amitchandak]

@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]

@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

[Greg_Deckler]

@sandee - See if this gets you there, otherwise @ me and I'll take a closer look https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

[sandee]

Hi Antriksh,

Super Duper Shot ........ Great ......

the file u attached this is exactly i am after, i have not applied in my file as it seems stuck, but i will get back to you on this. Many Many thanks for this.  God Bless You....

[AntrikshSharma]

@sandee Great, once you are able to replicate the same in your model and have no issues, then please mark my post as solved so that others can easily find the solution. Otherwise let me know if you still face any issue.

[sandee]

Hi Antriksh,

While accepting this as my solution i AM getting below error message again and again. i have checked everything is fine, but still same.

[AntrikshSharma]

@sandee, no problem, I will do it on your behalf 😄

[sandee]

Hi Greg,

thanks for the reference,

it seems nothing is working here, i am not getting the desired output. working on one formula from almost one week, really Fed up.

[AntrikshSharma]

@sandee does my solution work for you?