Forum Discussion
Need help with DAX Ranking logic for dynamic Field Parameters (Measure & Dimension)
Hi Community,
I am working on replicating a dynamic dashboard and have successfully set up three components:
Field Parameter for Dimensions: Swapping between 'Distributor' and 'Product'.
Field Parameter for Measures: Swapping between 'Policy Count' and 'Premium'.
Numeric Parameter: For a user-defined "Top N" value (e.g., Top 5, Top 10).
The Challenge: I am struggling to write a robust DAX Ranking measure that identifies the Top N when both the dimension and the measure are dynamic. My current RANKX attempts are failing because the "Table" argument in RANKX doesn't seem to update automatically when I switch the dimension slicer.
Current Setup:
Dimension Parameter Table: [Dimension Selection]
Measure Parameter Table: [KPI Selection]
Numeric Parameter: [Top N Value]
Does anyone have a recommended "best practice" or easy DAX approach to ensure the ranking updates correctly based on these two dynamic selections?
Thanks in advance!
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.
9 Replies
The dimension parameter doesn’t give you a “table” you can feed to RANKX. The stable pattern is to rank over the visual’s current categories using ALLSELECTED() on the parameter column, and evaluate the selected KPI via your measure parameter.
1) Base KPI measure (your measure parameter already does this)
Assume you already have a measure parameter like [KPI (Selected)] that returns Policy Count or Premium.
2) Rank measure (works with dynamic dimension + KPI)
Use the dimension parameter column (the one you put on the axis/rows), not Distributor/Product directly:
Rank (Dynamic) = RANKX( ALLSELECTED( 'Dimension Selection'[Dimension Selection] ), [KPI (Selected)], , DESC, Dense )Replace 'Dimension Selection'[Dimension Selection] with the actual field parameter column (often named something like 'Dim Param'[Dim Param]).
3) Top N filter measure
Show Top N = VAR N = SELECTEDVALUE( 'Top N Value'[Top N Value], 10 ) RETURN IF( [Rank (Dynamic)] <= N, 1, 0 )Then filter your visual with Show Top N = 1.
I created a category field prameter which has categorical columns in it.
Then i created KPI field parameters that has measure columns in it.
and created this KPI(selected) = selectedmeasure() DAX
when i write this Rank (dynamic) Dax messure it throws error:
RANKX(AllSELECTED('category field parameter'[Category Field],
[KPI(selected)],,DESC,
Dense)
this throws error when trying to add to the table, error says : column[category field] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.
tried finding alternative solutions but does work and making it more complex
Fixed the composite key issue, now getting all the rank as 1
Have you chosen any parameter value that returns the measure "[KPI (Selected)]".
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-pandeThe 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.
