Counting with (or in) switch

[Anonymous]

Hi there!

So I've got this Measure, it defines which machines on our machine list are 'Active'. The requirement for an active machine is that it's produced at least 25 widgets per week, for 3/4 of the last 4 weeks.
The measure currently works if I put it in a table with all the machine numbers, that way it calculates it for each machine. Problem with this is that I can't seem to get a Total for all the machine that are currently active. (Code for measure below)

ActiveMachines = CALCULATE(IF(
    (IF(CALCULATE(SUM('MetricsQuery'[TrayProduced Hourly]),
	          DATESINPERIOD('MetricsQuery'[TrayProducedDate].[Date]
                            ,CALCULATE(MAX('MetricsQuery'[TrayProducedDate].[Date]), FILTER('MetricsQuery', 'MetricsQuery'[TrayProduced Hourly]))
                            ,-7
                            ,DAY)
            ), 1) +  
    IF(CALCULATE(SUM('MetricsQuery'[TrayProduced Hourly]),
              DATESINPERIOD('MetricsQuery'[TrayProducedDate].[Date]
                            ,(CALCULATE(MAX('MetricsQuery'[TrayProducedDate].[Date]), FILTER('MetricsQuery', 'MetricsQuery'[TrayProduced Hourly])) - 7)
                            ,-7
                            ,DAY)
            ), 1) + 
    IF(CALCULATE(SUM('MetricsQuery'[TrayProduced Hourly]),
              DATESINPERIOD('MetricsQuery'[TrayProducedDate].[Date]
                            ,(CALCULATE(MAX('MetricsQuery'[TrayProducedDate].[Date]), FILTER('MetricsQuery', 'MetricsQuery'[TrayProduced Hourly])) - 14)
                            ,-7
                            ,DAY)
             ), 1) + 
    IF(CALCULATE(SUM('MetricsQuery'[TrayProduced Hourly]),
              DATESINPERIOD('MetricsQuery'[TrayProducedDate].[Date]
                            ,(CALCULATE(MAX('MetricsQuery'[TrayProducedDate].[Date]), FILTER('MetricsQuery', 'MetricsQuery'[TrayProduced Hourly])) - 21)
                            ,-7
                            ,DAY)
            ), 1) 
              >= 3), TRUE, FALSE))

I come from a python background so I thought my solution to this would be simply doing something like this:

VAR variable = 0
ActiveMachines2 = SWITCH([ActiveMachines]; TRUE; VAR variable += 1)

This obviously doesn't work in DAX
If I turn it in to just this:

ActiveMachines2 = SWITCH([ActiveMachines]; TRUE; 1)

It doesn't have any syntax errors etc etc, it also correctly assigns a 1 to each machine that is active. This is exactly where I'm stuck though, HOW in the WORLD do I count them??
Man I've been on this one for a while, I sincerely appreciate any help I can get 🙂

PS: If I need to clarify anything please ask! (although I am going offline soon so my response might take until the next day)

[TomMartens · Accepted solution]

Hey,

what you need is one of the table iterator functions. These functions iterate across a table, my favorite is SUMX.

Maybe this will do the trick:

no of active machines =
SUMX(
    VALUES('table'[machine])
    , IF(NOT(ISBLANK([ActiveMachines2])) , 1, BLANK()
)

Hopefully this provides you with some ideas, how to tackle your requirement.

Regards,

Tom 

[Anonymous]

Thank you SO very much!

For some reason on my side I can't give a TRUE and a FALSE option for my if statements, only a true option. But regardless I still got it to work with this, again thank you!