Forum Discussion
Calculate Rank from Virtual Table
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.
2 Replies
Anonymous , 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/367415amitchandak 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.
