Dimensions filtered by summarized measure values

[richard_wylde]

Hello, I have been shown this example   where the value of All Quotes reduces based on Rank By Category. However I need the values of Mar_Desc to disappear rather than reduce in size so in this example where rank_by_category = 231830, I would need Married & Single to disappear Do you know if this is possible ? Thank you

[v-rzhou-msft · Accepted solution]

Hi @richard_wylde 

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]

Thanks @v-rzhou-msft  - this works great!

Thank you

[amitchandak]

@richard_wylde ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
I checked the file. but not clear on the output
Appreciate your Kudos.

[richard_wylde]

Hi @amitchandak 

So in this screenshot I've highlighted in green the total values for Married & Single, and because these values are higher than the filtered Rank by Category (265381) then the All Quotes figures for Married & Single have been reduced to the Rank by Category figure (in red) . 

Instead I need the figures highlighted in red to disappear ( so only Married_Desc will show where All_Quotes is greater than Rank by Category)

 I hope this makes sense

Thanks