Summarized slicer

[richard_wylde]

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 

[v-rzhou-msft]

Hi @richard_wylde 

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. 

[richard_wylde]

Thanks @v-rzhou-msft  - 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

[v-rzhou-msft]

Hi @richard_wylde 

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

[richard_wylde]

Hi @v-rzhou-msft  - yes I've accepted the solution for  : Dimensions filtered by summarized measure values - thank you very much for your help !

[v-rzhou-msft · Accepted solution]

Hi @richard_wylde 

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. 

[richard_wylde]

Hi @v-rzhou-msft  - sure, thanks that's great