Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: Complex IF Statement

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Complex IF Statement

Wednesday

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?

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thursday

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thursday

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.

9 REPLIES 9

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thursday

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thursday

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thursday

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thursday

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thursday

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))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thursday

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Wednesday

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Wednesday

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?

Announcements

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

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

Top Solution Authors

User | Count |
---|---|

39 | |

23 | |

22 | |

19 | |

14 |

Top Kudoed Authors

User | Count |
---|---|

38 | |

22 | |

16 | |

15 | |

12 |