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

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,