Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Counting TRUEs in Measure

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 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Not applicable


@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
Not applicable

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
Not applicable

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 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.