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

Complex IF Statement

I have a table with a machine column and a minutes column. There are 9 different machines and the table is recording the events in which a machine was stopped and how long it was stopped for (the minutes column). 

 

Now, for just 4 different machines I need to return a value base on the minutes column. But the values returned depend on both the machine and the minutes. For example:

 

Machine 1 - If minutes between 0-3, I need to return 0. If minutes between 3-8, I need to return 1. If minutes between 8-13, I need to return 2. If minutes in between 13-18, I need to return 3. If minutes between 18-23, I need to return 4. If minutes between 23-28, I need to return 5. If minutes are above 28, I need to return 6. 

 

Machine 2 - If minutes between 0-2, return 0. If minutes between 2-7, return 1. If minutes between 7-12, return 2. If minutes between 12-17, return 3. If minutes between 17-22, return 4. If minutes between 22-27, return 5. If minutes are above 27 minutes, I need to return 6. 

 

Machine 3 - If minutes between 0-3, return 0. If minutes between 3-8, return 1. If minutes between 8-13, return 2. If minutes between 13-18, return 3. If minutes between 18-23, return 4. If minutes between 23-28. return 5. If minutes above 28 minues, rturn 6. 

 

Machine 4 - If minutes between 0-2, return 0. If minutes between 2-7, return 1. If minutes between 7-12, return 2. If minutes between 12-17, return 3. If minutes between 17-22, return 4, If minutes between 22-27 return 5. If minutes above 27 minutes retun 6. 

 

There are 9 machines, so the additonal 5 machines I just want all the display 0. 

 

Does anyone know how I write this? 

2 ACCEPTED SOLUTIONS

Hi @rebrow31 ,
 Make use of variables to do that:

Shots = 
Var Machine 1= IF(Downtime[Downtime] < 120, "0", IF(Downtime[Downtime] < 240, "1", IF(Downtime[Downtime] < 360, "2", IF(Downtime[Downtime] < 480, "3", IF(Downtime[Downtime] < 600, "4", IF(Downtime[Downtime] < 720, "5", IF(Downtime[Downtime] < 840, "6", IF(Downtime[Downtime] < 960, "7", IF(Downtime[Downtime] < 27000, "8"))))))))), IF(Downtime[Machine] = "DCM02", IF(Downtime[Downtime] < 300, "0", IF(Downtime[Downtime] < 600, "1", IF(Downtime[Downtime] < 900, "2", IF (Downtime[Downtime] < 1200, "3", IF (Downtime[Downtime] < 1500, "4", IF (Downtime[Downtime] < 1800, "5", IF (Downtime[Downtime] < 27000, "6"))))))))

Var Machine 2= conditions for machine 2
Var Machine 3= conditions for machine 2

Return  IF (Downtime[Machine] = "DCM01", Var Machine 1, IF (Downtime[Machine] = "DCM02",Var Machine 1,
 IF (Downtime[Machine] = "DCM03", Var Machine 3)))

 

 

 

I hope this helps!

View solution in original post

daxer
Solution Sage
Solution Sage

@rebrow31 

If all you want is a calculated column, then you should do it the way I'm going to describe. You should create a  MachineConfig table with the following columns:

Machine|LowerBound|UpperBound|Value
------------------------------------
Machine1|0|3|0
Machine1|3|8|1
...
Machine2|0|2|0
Machine2|2|7|1
...
This is what it should look like in a good professional model.

Once you've got this table, it's dead easy to add a column with the right Value's to your table T.

 

 

[Value] = /// calculated column in T
var TheMachine = T[Machine]
var TheMinutes = T[Minutes]
var TheValue =
	MAXX(
		FILTER(
			// This filter should return just
			// one row if everything is correctly
			// coded.
			MachineConfig,
			MachineConfig[Machine] = TheMachine
			&&
			// You should decide which bound
			// is inclusive and which is exclusive.
			Machine[LowerBound] <= TheMinutes
			&&
			TheMinutes < Machine[UpperBound]
		),
		MachineConfig[Value]
	)
return
	TheValue

 

 

The code will, of course, adjust itself automatically to the entries in the MachineConfig table.

View solution in original post

9 REPLIES 9
daxer
Solution Sage
Solution Sage

@rebrow31 

If all you want is a calculated column, then you should do it the way I'm going to describe. You should create a  MachineConfig table with the following columns:

Machine|LowerBound|UpperBound|Value
------------------------------------
Machine1|0|3|0
Machine1|3|8|1
...
Machine2|0|2|0
Machine2|2|7|1
...
This is what it should look like in a good professional model.

Once you've got this table, it's dead easy to add a column with the right Value's to your table T.

 

 

[Value] = /// calculated column in T
var TheMachine = T[Machine]
var TheMinutes = T[Minutes]
var TheValue =
	MAXX(
		FILTER(
			// This filter should return just
			// one row if everything is correctly
			// coded.
			MachineConfig,
			MachineConfig[Machine] = TheMachine
			&&
			// You should decide which bound
			// is inclusive and which is exclusive.
			Machine[LowerBound] <= TheMinutes
			&&
			TheMinutes < Machine[UpperBound]
		),
		MachineConfig[Value]
	)
return
	TheValue

 

 

The code will, of course, adjust itself automatically to the entries in the MachineConfig table.

View solution in original post

rebrow31
Frequent Visitor

I've managed to come up with this which works fine: 

 

Shots = IF (Downtime[Machine] = "DCM01", IF(Downtime[Downtime] < 120, "0", IF(Downtime[Downtime] < 240, "1", IF(Downtime[Downtime] < 360, "2", IF(Downtime[Downtime] < 480, "3", IF(Downtime[Downtime] < 600, "4", IF(Downtime[Downtime] < 720, "5", IF(Downtime[Downtime] < 840, "6", IF(Downtime[Downtime] < 960, "7", IF(Downtime[Downtime] < 27000, "8"))))))))), IF(Downtime[Machine] = "DCM02", IF(Downtime[Downtime] < 300, "0", IF(Downtime[Downtime] < 600, "1", IF(Downtime[Downtime] < 900, "2", IF (Downtime[Downtime] < 1200, "3", IF (Downtime[Downtime] < 1500, "4", IF (Downtime[Downtime] < 1800, "5", IF (Downtime[Downtime] < 27000, "6")))))))))
 
However I need to insert 2 more machines in there and it isn't letting me do this because IF function can only have 3 arguments. Which I get but how do I go round being able to put additional rules in for 2 more machines? 

@rebrow31 

 

Your code is totally unmaintainable. By writing it like this you're shooting yourself in the foot. There is a much better way to write it.

Hi @rebrow31 ,
 Make use of variables to do that:

Shots = 
Var Machine 1= IF(Downtime[Downtime] < 120, "0", IF(Downtime[Downtime] < 240, "1", IF(Downtime[Downtime] < 360, "2", IF(Downtime[Downtime] < 480, "3", IF(Downtime[Downtime] < 600, "4", IF(Downtime[Downtime] < 720, "5", IF(Downtime[Downtime] < 840, "6", IF(Downtime[Downtime] < 960, "7", IF(Downtime[Downtime] < 27000, "8"))))))))), IF(Downtime[Machine] = "DCM02", IF(Downtime[Downtime] < 300, "0", IF(Downtime[Downtime] < 600, "1", IF(Downtime[Downtime] < 900, "2", IF (Downtime[Downtime] < 1200, "3", IF (Downtime[Downtime] < 1500, "4", IF (Downtime[Downtime] < 1800, "5", IF (Downtime[Downtime] < 27000, "6"))))))))

Var Machine 2= conditions for machine 2
Var Machine 3= conditions for machine 2

Return  IF (Downtime[Machine] = "DCM01", Var Machine 1, IF (Downtime[Machine] = "DCM02",Var Machine 1,
 IF (Downtime[Machine] = "DCM03", Var Machine 3)))

 

 

 

I hope this helps!

View solution in original post

Thank you so much for your help. It makes sense, I had no idea about variables. I am still getting an error messgae though, could you shed any light on what might be wrong?:

 

Shots =
Var Machine 1= IF(Downtime[Downtime] < 2, "0", IF(Downtime[Downtime] < 4, "1", IF(Downtime[Downtime] < 6, "2", IF(Downtime[Downtime] < 8, "3", IF(Downtime[Downtime] < 10, "4", IF(Downtime[Downtime] < 12, "5", IF(Downtime[Downtime] < 14, "6", IF(Downtime[Downtime] < 16, "7", IF(Downtime[Downtime] < 450, "8")))))))))

Var Machine 2= IF(Downtime[Downtime] < 5, "0", IF(Downtime[Downtime] < 10, "1", IF(Downtime[Downtime] < 15, "2", IF (Downtime[Downtime] < 20, "3", IF (Downtime[Downtime] < 25, "4", IF (Downtime[Downtime] < 30, "5", IF (Downtime[Downtime] < 450, "6")))))))

Var Machine 3= IF(Downtime[Downtime] < 3, "0", IF(Downtime[Downtime] < 8, "1", IF(Downtime[Downtime] < 13, "2", IF (Downtime[Downtime] < 18, "3", IF(Downtime[Downtime] < 23, "4", IF(Downtime[Downtime] < 28, "5", IF(Downtime[Downtime] < 450, "6")))))))

Var Machine 9= IF(Downtime[Downtime] < 2, "0", IF(Downtime[Downtime] < 7, "1", IF(Downtime[Downtime] < 12, "2", IF(Downtime[Downtime] < 17, "3", IF(Downtime[Downtime] < 22, "4", IF(Downtime[Downtime] < 27, "5", IF(Downtime[Downtime] < 450, "6")))))))

Return IF (Downtime[Machine] = "DCM01", Var Machine 1, IF (Downtime[Machine] = "DCM02", Var Machine 2, IF (Downtime[Machine] = "DCM03", Var Machine 3, IF (Downtime[Machine] = "DCM09", Var Machine 9))))

@rebrow31 , The variable names could be a problem...should be a single string. My bad.
Machine_1, Machine_2.

Also, give a try for @daxer 's solution.

@Tanushree_Kapse 

 

Again, please do not write code like this as it's completely unmaintainable and plain ugly.

daxer
Solution Sage
Solution Sage

We still don't know if you need a measure or a calculated column...

rebrow31
Frequent Visitor

Well that is because I do not know. I am not good on Power BI with this type of thing, which is why I am asking for some help. Maybe you could suggest which should be better?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors