Forum Discussion

Swam80's avatar
Swam80
Frequent Visitor
2 years ago

Dynamic Ranking for legend.

Hello all,   So I have been working on a powerbi project and I am new to this so just exploring. I have orders table, customers table,date table along with few other tables and I have created measu...
legend
power bi
Ranks
Swam80's avatar
Swam80
Frequent Visitor
2 years ago

Nope, didnt work. IT gives all customers same value.
I tried one manual alternative, date on x axis, Selected_Metric_Value on y axis, legend as customer name. AND on customer_name filter I did topN filter option, so I set top 5 and in "by" option i used Selected_Metric_Value. It works.

But I wanted to know if using measures canwe do it. Like I want to apply a Parameter slicer so that I can vary the N in top N. Now i have manually set N as 5

DallasBaba's avatar
DallasBaba
Skilled Sharer
2 years ago

Swam80 Let's modify the measures to make it work more dynamically with a parameter slicer.

 

You can create a parameter table to store the value of N and then reference that parameter in your measures.

ParameterTable = DATATABLE(
    "Parameter", STRING,
    {{"TopN", "5"}}  -- Set the default value of N here
)

Modify the Rank_Per_Customer measure to use the parameter:

Rank_Per_Customer = 
RANKX(
    ALL(dim_customers[customer_name]), 
    [Selected_Metric_Value], 
    , DESC, Dense
)

Modify the Top_N_Customers_Metric measure to reference the parameter:

Top_N_Customers_Metric = 
CALCULATE(
    [Selected_Metric_Value],
    FILTER(
        ALL(dim_customers[customer_name]),
        [Rank_Per_Customer] <= VALUES(ParameterTable[TopN])
    )
)

Now, you can use the ParameterTable[TopN] in a slicer to dynamically change the value of N. This way, you only need to adjust the parameter to control the top N customers in your line chart.

 

Ensure that the ParameterTable[TopN] column format is set to Whole Number in the Power BI model for correct filtering behavior.

 

I hope this provides the flexibility you want, allowing you to vary the N using a parameter slicer.