Forum Discussion
Need help with DAX Ranking logic for dynamic Field Parameters (Measure & Dimension)
Hi Durgapb129,
Thank you for posting your query in Microsoft Fabric Community Forum. Also, thanks to Kedar_Pande , cengizhanarslan for those inputs on this thread.
Yes, you are correct about 'Dimension Selection'[Dim] that column is created automatically when you create the dimension field parameter, and that is the right one to use in RANKX.
About SELECTEDVALUE 'KPI Selection'[KPI] this KPI column also comes from the KPI field parameter. It’s not a custom calculation. It just contains the display names like Policy count and Premium that you see in the slicer. We use it only to know which KPI is selected, not for ranking directly.
The main issue you are facing is because field parameters cannot be ranked directly. When you use the KPI field parameter inside RANKX, Power BI cannot evaluate it properly, so you either get errors or all ranks as 1.
That’s why the working approach is:
Use the dimension field parameter column inside ALLSELECTED() for ranking. Use actual base measures Policy Count, Premium inside RANKX, Use SELECTEDVALUE 'KPI Selection'[KPI] only in a SWITCH to decide which base measure to rank.
If you try to use field parameters for both dimension and KPI inside RANKX, the rank will not work correctly.
Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Community Support Team.
Dynamic Rank =
SWITCH(
SELECTEDVALUE('KPI Selection'[KPI]),
"Policy Count", RANKX(ALLSELECTED('Dimension Selection'[Dim]), [Policy Count], , DESC, Dense),
"Premium", RANKX(ALLSELECTED('Dimension Selection'[Dim]), [Premium], , DESC, Dense),
BLANK()
)
Show Top N = [Dynamic Rank] <= [Top N Value]
Visual filter: [Show Top N] = TRUE
If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande