Forum Discussion
Summarized slicer
In the Slicer Advice it mentions the following limittaions :-
- The numeric range slicer filters every underlying row in the data, not any aggregated value.
Is there a way around this using DAX ?
In this example I wish the range to be from 1 to 649021, and for Mar_Desc to be filtered based on the summarized All_Quotes value.
Thank you
For finishing this post, we need to mark an answer as a sloution.
So here I will copy my answer in Dimensions filtered by summarized measure values and mark it as a solution.
I use measure to achieve your goal.
Measure1:
Measure1 = CALCULATE(SUM(Query1[All Quotes]),FILTER(ALL(Query1),Query1[Mar_Desc]=MAX(Query1[Mar_Desc])))Measure2:
Measure2 = IF( [Measure1]<= MAX(Query1[Rank by category]),[Measure1],BLANK())We build a new table visual by Mar_Desc and Measure2. Then select 231830 in Slicer.
Result is as below:
You can download the pbix file from this link: Dimensions filtered by summarized measure values
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Here I have some advice. If you want to use the slicer range to be from 1 to 649021, you may try to use index column to build a slicer.
And I try to build two calculated columns to build slicers.
sum = CALCULATE(SUM(Query1[All Quotes]),FILTER(ALL(Query1),Query1[Mar_Desc]=EARLIER(Query1[Mar_Desc])))Rank by category = RANKX(FILTER(Query1,Query1[Mar_Desc]=EARLIER(Query1[Mar_Desc])),Query1[Index],,ASC)Result:
Rank by category will build a rank for each category in Macr_Desc column.
Sum will show the summarize for each category in Macr_Desc column.
If this reply still couldn’t help you to solve the problem, please provide me more details or show me the result visual you want.
You can download the pbix file from this link: Summarized slicer
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Anonymous - that's great ! ( I was previously told this couldn't be done)
The only addition I need to make is if the All_Quotes are less than the Rank_By Category I need them to disappear rather than reduce in size, so in this example rank_by_category = 231830, I would need Married & Single to disappear, is this easily done ?
Many thanks for you help
Your problem may have been solved in your new community. Link: Dimensions filtered by summarized measure values
Please kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your onedrive business.
Best Regards,
Rico Zhou
