Get Max Bin Value via Measure based on Filter

[JF_93]

Hi All,

I struggle a lot with the following. I can visualize a bar plot based on one binned table column and a measure that distributes its values over the bins. This looks as follows. Now I would like to calculate a measure that is returning me the max Bin value with respect to a filtered value from my measure on the y-axis.

The Image shows the desired result of my measure: I want to get the max Bin value for all values of my Measure (y-axis) that are greater or equal than 13. The desired result would return 13,5.

I tried it with the following pseudo code:

CALCULATE(MAX(data[Values (bins)]), FILTER(data, data[Values (Measure)] >=13))

However, I am getting a wrong returned value of 9,5...

I would be really happy, if you have a good hint for solving this issue! Many thanks in advance!

Show and Tell, Tips and Tricks, Need Help

[JF_93 · Accepted solution]

Hi @v-yanjiang-msft ,

I came up with a good solution for my problem:

MAXX(FILTER(SUMMARIZE(data, data[Values (bins)], "Measure Bins", data[Values (Measure)]), [Measure Bins] >= 13),[Values (bins)])

 The issue was that I needed to group the Measure Values to the binned Values first...

[JF_93]

Hi @v-yanjiang-msft,

thank you for the quick reply. Unfortunately, the table that I am working with already has some filters applied. In order for this to work, I think that I cannot use ALL, since I do not want to lose the filters.

[JF_93]

So in this case I think I would need a preprocessed table to filter from that has already these Bar-plot like values cummulated...

[v-yanjiang-msft]

Hi @JF_93 ,

If you want to take the slicer into consideration, replace ALL with ALLSELECTED. Or if you want to keep the filter with a specific column. use ALLEXCEPT function.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yanjiang-msft]

Hi @JF_93 ,

As measures are calculated based on context, usually we should use ALL function when filter table.

Modify the formula to:

MAXX ( FILTER ( ALL ( data ), data[Values (Measure)] >= 13 ), [Values (bins)] )

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.