Counting TRUEs in Measure

[Anonymous]

So I've got this piece of code that checks which machines we made are currently active. The current standard for an active machine is that it produced 25 widgets per week at least, for the the last four weeks straight. Writing this in DAX wasn't that hard, but we're not happy with the definition. Current problem is that if the workes for one machine take a week off the machine now counts as inactive, even though its just one week < 25 widgets. What we now want to do is change the definition to 3/4 weeks for the past four weeks. This way a machine isnt marked inactive over one slow week.

This turned out to be harder than I thought. My initial idea was just making a VAR counter = 0 and just counting each TRUE for each week, if the counter is 3 or above the machine is active. Problem is I just cannot figure out how to use VARs in DAX, I just dont get it.

ActiveMachines = IF( 
    (CALCULATE(SUM('Query1'[TrayProduced Hourly]),
              DATESINPERIOD('Query1'[TrayProducedDay].[Date]
                            ,CALCULATE(MAX('Query1'[TrayProducedDay].[Date]), FILTER(ALL('Query1'), 'Query1'[TrayProduced Hourly]))
                            ,-7
                            ,DAY)
             ) > 25 &&  
    CALCULATE(SUM('Query1'[TrayProduced Hourly]),
              DATESINPERIOD('Query1'[TrayProducedDay].[Date]
                            ,(CALCULATE(MAX('Query1'[TrayProducedDay].[Date]), FILTER(ALL('Query1'), 'Query1'[TrayProduced Hourly])) - 7)
                            ,-7
                            ,DAY)
             ) > 25 && 
    CALCULATE(SUM('Query1'[TrayProduced Hourly]),
              DATESINPERIOD('Query1'[TrayProducedDay].[Date]
                            ,(CALCULATE(MAX('Query1'[TrayProducedDay].[Date]), FILTER(ALL('Query1'), 'Query1'[TrayProduced Hourly])) - 14)
                            ,-7
                            ,DAY)
             ) > 25 && 
    CALCULATE(SUM('Query1'[TrayProduced Hourly]),
              DATESINPERIOD('Query1'[TrayProducedDay].[Date]
                            ,(CALCULATE(MAX('Query1'[TrayProducedDay].[Date]), FILTER(ALL('Query1'), 'Query1'[TrayProduced Hourly])) - 21)
                            ,-7
                            ,DAY)
             ) > 25), TRUE, FALSE)

The code above currently works, now what would I need to add to this (or remove, or whatever else) to get my idea to work here? Any help would be greatly appreciated 🙂

[Anonymous · Accepted solution]

You could do something like this:

IF (

IF ( ProductionWeek1 > 25, 1, 0) + IF ( ProductionWeek2 > 25, 1, 0 ) + IF ( ProductionWeek3 > 25, 1, 0 ) + IF ( ProductionWeek4 > 25, 1, 0 ) >= 3,

"Active",

"Inactive"

)

Might not be the most efficient way to do this, but works nevertheless. Of course you have to replace "ProductionWeek..." with your formula.

[Anonymous]

---

@Anonymous wrote:

You could do something like this:

IF (

IF ( ProductionWeek1 > 25, 1, 0) + IF ( ProductionWeek2 > 25, 1, 0 ) + IF ( ProductionWeek3 > 25, 1, 0 ) + IF ( ProductionWeek4 > 25, 1, 0 ) >= 3,

"Active",

"Inactive"

)

Might not be the most efficient way to do this, but works nevertheless. Of course you have to replace "ProductionWeek..." with your formula.

---

Yeah, I was thinking of doing something like that too, with 1 and 0 instead of TRUE and FALSE. Only for some reason DAX is only allowing me to do TRUE and FALSE, if I enter anything else in those two spots it throws a fit. I'll use this if I can get around that though

[Anonymous]

Well, that sounds odd to me, IF should obviously not behave this way. Have you tried replacing your condition with something simpler and expanding it step by step until you reach the condition you want to have? Maybe there is a misplaced comma somewhere that changes the evaluation or something similar. Doesn't look like that is the case, but you never know. Anyway you'd get a closer grab on the problem this way.

[Anonymous]

It ended up working with a small edit, it somehow worked with just 1 insted of 1, 0. I really dont understand why but for some reason it just doesnt except anything other than FALSE as the option for FALSE. Thanks for the help 🙂