Calculate Rank from Virtual Table

[jyou09]

Hi, I have a virtual table (vTable) with columns PaymentId (unique key), BusinessUnitId and CompanyId. I'm trying to calculate volume ranking of companies by the following -

RANKX(ALL('Company'), CALCULATE(COUNTX(vTable, [PaymentId])),, DESC, Skip)

But it returns 1 for all companies. Is it because there is no relationship between the virtual table and Company table? What will be the workaround for this situation?

Thanks.

[amitchandak]

@jyou09 , What ever column you want to use in visual, they should be part of Rankx or the table should be used in rankx.

In this case, you need to create a rank across related tables or columns from the table

RANKX(ALL('Company'[PaymentId], 'Company'[BusinessUnitId], 'Company'[CompanyId]), CALCULATE(COUNTX(vTable, [PaymentId])),, DESC, Skip)

Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA

Measure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40

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

[jyou09]

@amitchandak Thanks for replying my question. My situation is actually a little bit complicate -

My model (Azure Analysis Services) has the three tables:

Company - CompanyId, CompanyName

BusinessUnit - BusinessUnitId, CompanyId, BusinessUnitName

Payment - PaymentId, BusinessUnitId, CompanyId, Amount

BusinessUnit has only the rows for current login company user. There is no relationship between it and other tables.

Payment is a fact table. It has relationship to Company table.

The requirement is - If I login as company A user, I will be able to choose multiple business units of my company from slicer to compare with all payments from other companies.

So I did this -

VAR T1 = CALCULATETABLE(Payment,EXCEPT(VALUES(Payment[CompanyId]),VALUES(BusinessUnit[CompanyId])))
VAR T2 = CALCULATETABLE(Payment,TREATAS(VALUES(BusinessUnit[BusinessUnitId]),Payment[BusinessUnitId]))

VAR vTable = UNION(T1, T2)

T1 is to get the dataset for all other companies. T2 is to get the dataset for selected business units of my company. Then I combine the dataset to be vTable.

Now I can get the row count and amount correct by - COUNTX(vTable,[PaymentId]) and SUMX(vTable,[Amount]). But the rank is too hard for me. I'm still new to DAX and Power BI. So please help.