Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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])
Rank = RANKX(ALL(carrier_attributes),SUM(carrier_attributes[Call Limit]),,DESC)
Rank = RANKX(ALLSELECTED(carrier_attributes),CALCULATE(SUM(carrier_attributes[Call Limit])),,DESC)Best Regards,
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
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.
I created a measure based on carrier_attributes[Call Limit]:
Rank = RANKX(ALL(carrier_attributes),SUM(carrier_attributes[Call Limit]),,DESC)
But I think Measure cannot be used in a slicer. I want to display the number in a slicer so that the user can dynamically select the data and export the ppt.
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])
Rank = RANKX(ALL(carrier_attributes),SUM(carrier_attributes[Call Limit]),,DESC)
Rank = RANKX(ALLSELECTED(carrier_attributes),CALCULATE(SUM(carrier_attributes[Call Limit])),,DESC)Best Regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |