Measure should evaluate to True if any selected value is True

[Tom_G]

Hello

I have a visual table like the following which is filtered by a shop slicer. The Measure is supposed to show TRUE if a shop is open more than 60% of the hours in a month.

Shop	Open Hours	Remaining Monthly Hours	Measure
Shop A	148	596	FALSE
Shop B	463	281	TRUE
TOTAL	611	877	FALSE

What I need is a measure that evaluates the total to TRUE if one of the selected fields evaluates to TRUE. Here the measure evaluates to False because the total of Open Hours is not 60% of the total number of hours in 2 months. 

The measure I currently have is this

Measure = Sum of Open hours measure >=([Sum of Open Hours measure + Sum of Remaining monthly hours measure])/100*60

 Any ideas how I can change this to make it work?

Any help much appreciated!

[Anonymous · Accepted solution]

Hi @Tom_G ,

Please create another measure as below to replace the current measure in visual:

Measure2 = MAXX ( VALUES ( 'Shop'[Shop] ), IF ( [Measure] = TRUE (), "True", "False" ) )

Best Regards

[Tom_G]

Hi @Anonymous - that works very well. Thanks a lot for your help!

[selimovd]

Hey @Tom_G ,

your question is a little confusing.

Is [Measure] a calculated column or a measure?

"Sum of a measure" doesn't work in DAX in a measure. Please post the actual formula of your [Measure] and not some pseudocode.

And then please give an example how your result should look like. To be hones, I didn't understand what you want to achieve or what doesn't work.

Thank you and best regards

Denis

[Tom_G]

Hi @selimovd , sorry if it wasn't clear. The Measure is a measure.
To give some more detail - the sum of open hours measure = CALCULATE(SUM(TableColumn(OpenHours).

Sum of Remaining monthly hours is basically Monthly Hours Measure - sum of open hours measure

Monthly Hours Measure = CALCULATE(SUM(TableColumn(MonthlyHours).

What I want is a card that displays TRUE even when Shop A and Shop B are filtered because Shop A evaluated to TRUE. Hope this is clear. Thanks

[selimovd]

@Tom_G 

And what is the formula for [Measure] that returns true or false?

[Tom_G]

Hi @selimovd 

The formula for the measure that returns true or false is 

Measure = Sum of Open hours measure >=([Sum of Open Hours measure + Sum of Remaining monthly hours measure])/100*60

So it is a measure built upon other measures previously explained. I think the issue is that though the visual is slicing the data by shop the measure is summing the total hours and remaining hours.

Therefore when multiple shops are selected in the slicer it looks at whether the total number of hours for the shops selected is greater than or equal to 60% of the total selected hours. 

How to make the measure work on the principle that if only one of the selected shops is open more than 60% of hours in the time period, it returns TRUE?