topic Create a measure of a measure: when greater than x then 1 else 0 in DAX Commands and Tips

Create a measure of a measure: when greater than x then 1 else 0

acg — Tue, 12 Oct 2021 03:16:28 GMT

I have some IDs for which I calculated the average time worked.

Average overtime looks simply like this:

Average Overtime worked=

AVERAGE(timesheet[Weekly_OverTimeHours])

How can I create a new measure called

Indicator= when [Average Overtime worked] > 5 then 1 else 0 ?

I know I could use the filter, but I need to have Indicator to create a visual subsequently.

ID	Average Overtime worked	Indicator
1273	8	1
4476	4	0
5623	6	1
2233	5	0
4423	7	1

Re: Create a measure of a measure: when greater than x then 1 else 0

amitchandak — Tue, 12 Oct 2021 03:30:43 GMT

acg , Create a new table like Indicator with values 1,0 and use that as slicer

Now create a measure like

calculate([Average Overtime worked]. filter(Values(Table[ID]), [Indicator] = max(Indicator[Indicator])))

Refer this, if needed

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Re: Create a measure of a measure: when greater than x then 1 else 0

acg — Tue, 12 Oct 2021 05:40:55 GMT

Does this also work for numeric outcomes? 0/1 needs to be numeric so that it can be used later on for some calculations / visuals, where the indicator needs to be counted.

Re: Create a measure of a measure: when greater than x then 1 else 0

acg — Tue, 12 Oct 2021 04:29:34 GMT

Great amitchandak,

I see you create the buckets and that is how you create the indicator.

So I created this as in:

Margin Type =

SWITCH(TRUE(),

[AVG OT Measure] > 5, 1,

[AVG OT Measure] = 5, 1,

[AVG OT Measure] <5, 0)

But it says: The expression specified in the query is not a valid table expression.

Why would that be? THe indivator 0/1 needs to be numeric, so it can be used later on.

Re: Create a measure of a measure: when greater than x then 1 else 0

v-yingjl — Fri, 15 Oct 2021 01:48:59 GMT

Hi acg ,

The [Margin Type] formula you have created could not be used as a calculated table, it should be a measure or a calculated column.

Measure:

Margin Type = IF( [Average overtime worked] > 5,1,0 )

Calculated column:

Margin Type column = VAR _avg = CALCULATE ( AVERAGE ( timesheet[Weekly_OverTimeHours] ), ALLEXCEPT ( 'timesheet', timesheet[ID] ) ) RETURN IF ( _avg > 5, 1, 0 )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Create a measure of a measure: when greater than x then 1 else 0

acg — Wed, 20 Oct 2021 00:23:00 GMT

Hi @

So I need a total value of 3 in that example, as I need to be able to use this to create a visual.

I tried to pack your query into a sumx, but I cannot get it working.

Do you have an idea how to do that?

PREVIEW

Re: Create a measure of a measure: when greater than x then 1 else 0

rickrosa — Fri, 04 Aug 2023 17:36:50 GMT

Hi acg , any luck with the last part of your problem? I am trying to solve the exact same scenario as you were.