Dynamic Row-Level Ranking Based on Parameter Selection in Power BI

[Asha31]

Hi Power BI Community,
Please find Image for reference like how user wanted to see final result.

I'm trying to implement dynamic row-level ranking in Power BI using two parameters: Details 1 and Details 2. These parameters are used to switch between columns from three different related tables. Relationships between the tables are correctly defined, and the parameters are working as expected for filtering and visuals.

Now, I want to create a ranking measure that dynamically ranks rows based on the selected parameter value.

I understand that pivot/ unpivot won't help here since the parameter is switching between columns from different tables and both Parameter has a same column. Have tried using RANKX, but the results are not dynamic or context-aware when used in visuals like matrix and tooltip.

Is it possible to achieve dynamic row-level ranking in Power BI using DAX and parameter selection across multiple tables? If so, what would be the best approach?

Thanks in advance for your guidance!

[v-hashadapu]

Hi @Asha31 , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.

[v-hashadapu]

Hi @Asha31 , Thank you for reaching out to the Microsoft Community Forum.

We find the answer shared by @DataNinja777  is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.
Thank you @DataNinja777  for your valuable response.

[v-hashadapu]

Hi @Asha31 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[DataNinja777 · Accepted solution]

Hi @Asha31 ,

You can absolutely achieve dynamic row-level ranking in Power BI based on a parameter that switches between columns from different tables. The best way to accomplish this is by combining a disconnected parameter table for user selection with two core DAX measures: one to dynamically display the selected value and another to dynamically calculate its rank. This approach makes your visuals interactive and context-aware.

First, you'll need to create a simple, disconnected table to act as the source for a slicer. You can do this using the "Enter data" feature on the Home ribbon. Create a single-column table, perhaps named Parameter Selection, with a column called Selection. Each row in this column should contain the name of a metric you want the user to be able to choose from, such as "Member Count," "Metric A," or "Metric B." This table should not have any relationships with your other data models; it serves only as a control panel for your measures.

Next, you will create a "dispatcher" measure that uses the selection from the parameter table to determine which underlying value to display. This measure uses a SWITCH function to check the value selected in your slicer and then returns the result of the corresponding base measure (e.g., [Total Member Count], [Total Metric A]). This central measure is what you will place in your matrix to show the numerical values.

Selected Value Measure = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Parameter Selection'[Selection]) = "Member Count", [Total Member Count],
    SELECTEDVALUE('Parameter Selection'[Selection]) = "Metric A", [Total Metric A],
    SELECTEDVALUE('Parameter Selection'[Selection]) = "Metric B", [Total Metric B],
    BLANK()
)

With the value measure in place, you can now create the dynamic ranking measure. This measure uses the RANKX function to rank the result of your [Selected Value Measure]. The key is to define the ranking scope using ALLSELECTED, which tells RANKX to rank the current row's value against all other rows currently visible in the visual, respecting any active filters. Assuming your matrix rows are based on a column like 'YourDimensionTable'[Dimension], the DAX would look like this.

Dynamic Rank = 
IF(
    NOT ISBLANK([Selected Value Measure]),
    RANKX(
        ALLSELECTED('YourDimensionTable'[Dimension]),
        [Selected Value Measure],
        , 
        DESC, 
        Dense
    )
)

Finally, to build the visual, you would place your categories on the Columns, your dimension on the Rows, and both the [Selected Value Measure] and [Dynamic Rank] measures into the Values field of a matrix. To achieve the stacked layout shown in your image, go to the "Format your visual" pane, find the "Values" section, and switch the "Show on rows" option to On. Now, when a user makes a selection in your slicer, both the values and their corresponding ranks in the matrix will update dynamically.

Best regards,