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
The place where i'm experiencing issue is SELECTEDVALUE('KPI SELECTION'[KPI]), this KPI column what is this? Is this the KPI field parameter which is generated after you created the KPI parameter or some sort of calculation we have written for this?
'Dimension Selection'[Dim] is the field parameter which is generated from the parameter created right ?
coz I tried using parameter field for both the categorical parmater and KPI paramter and it doesn't show me any proper rank result
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.Hi Durgapb129,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Regards,
Community Support Team.
