Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |