cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GotYaRG Frequent Visitor
Frequent Visitor

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

Accepted Solutions
MoOv Regular Visitor
Regular Visitor

Re: Counting TRUEs in Measure

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
MoOv Regular Visitor
Regular Visitor

Re: Counting TRUEs in Measure

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

GotYaRG Frequent Visitor
Frequent Visitor

Re: Counting TRUEs in Measure


@MoOv 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

MoOv Regular Visitor
Regular Visitor

Re: Counting TRUEs in Measure

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.

GotYaRG Frequent Visitor
Frequent Visitor

Re: Counting TRUEs in Measure

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 423 members 3,865 guests
Please welcome our newest community members: