topic Re: RANX Function in DAX Commands and Tips

RANX Function

sandee — Fri, 04 Sep 2020 15:15:09 GMT

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 .

Re: RANX Function

Greg_Deckler — Fri, 04 Sep 2020 15:38:56 GMT

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

Re: RANX Function

amitchandak — Fri, 04 Sep 2020 15:48:53 GMT

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/367415

Re: RANX Function

sandee — Sat, 05 Sep 2020 08:54:12 GMT

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 ?

Re: RANX Function

amitchandak — Sat, 05 Sep 2020 09:26:01 GMT

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

Re: RANX Function

AntrikshSharma — Sat, 05 Sep 2020 10:07:38 GMT

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

Re: RANX Function

sandee — Sat, 05 Sep 2020 12:30:45 GMT

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.

Re: RANX Function

AntrikshSharma — Sat, 05 Sep 2020 12:44:39 GMT

sandee does my solution work for you?

Re: RANX Function

sandee — Sat, 05 Sep 2020 12:45:13 GMT

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

Re: RANX Function

AntrikshSharma — Sat, 05 Sep 2020 13:55:13 GMT

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.

Re: RANX Function

sandee — Sat, 05 Sep 2020 17:39:27 GMT

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.

Re: RANX Function

AntrikshSharma — Sat, 05 Sep 2020 17:55:37 GMT

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

Re: RANX Function

AntrikshSharma — Sun, 06 Sep 2020 13:23:16 GMT

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