Forum Discussion
Top N selection based on filter
I have a query regarding the Top N filter.
I have a table in PowerBI which I want to split (when exported to Powerpoint the whole table is not exported).
So I decided to provide a slicer on top for Top N (in-between) values. So that the user can manually set the values in between them.
I have created a TOP N column as mentioned below:
Top N = RANKX(carrier_attributes,carrier_attributes[Carrier Name],,DESC,Dense)
I expect the output of N entries when a specific Carrier Name with N entries is selected, i.e. the TOP N dynamically changing with Column [Carrier Name] filter
hi, Anonymous
First, you should know that calculated column and calculate table can't be affected by any slicer. you could create a measure instead of column.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/Second, based on your measure, you could add a what if parameter as a slicer.
Then add a measure
TopN = IF([Rank]<=[Parameter Value],[Rank])
Here is a simple sample pbix file, please try it.By the way, your measureRank = RANKX(ALL(carrier_attributes),SUM(carrier_attributes[Call Limit]),,DESC)
is wrong, if this rank measure could meet you.Rank = RANKX(ALLSELECTED(carrier_attributes),CALCULATE(SUM(carrier_attributes[Call Limit])),,DESC)
Best Regards,
Lin
4 Replies
Hi
Columns in PowerBI are unaffected by slicers because they are pre-calculated when the dataset is refreshed. You will need to use a measure for this.
hi, Anonymous
First, you should know that calculated column and calculate table can't be affected by any slicer. you could create a measure instead of column.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/Second, based on your measure, you could add a what if parameter as a slicer.
Then add a measure
TopN = IF([Rank]<=[Parameter Value],[Rank])
Here is a simple sample pbix file, please try it.By the way, your measureRank = RANKX(ALL(carrier_attributes),SUM(carrier_attributes[Call Limit]),,DESC)
is wrong, if this rank measure could meet you.Rank = RANKX(ALLSELECTED(carrier_attributes),CALCULATE(SUM(carrier_attributes[Call Limit])),,DESC)
Best Regards,
Lin
hi, Anonymous
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Best Regards,
Lin
