Forum Discussion
Solved
Group data based on multiple conditions
All, I could use some help with this problem. I have a large table with "Mail type" and 'Delivery time" and I want to group the data or add a column retuning "Group" value based on the reference table below:
| Mail type | Delivery time min | Delivery time max | Group |
| 1st class | 1 | 4 | great |
| 1st class | 5 | 7 | good |
| 1st class | 8 | bad | |
| Priority | 1 | 3 | great |
| Priority | 4 | 6 | good |
| Priority | 7 | bad | |
| Express | 0 | 1 | great |
| Express | 2 | 3 | good |
| Express | 4 | bad |
What are my options?
Many thanks
BInovice So what are the rules? You could do something like:
Group = SWITCH(TRUE(), [Mail type] = "1st class" && [Delivery time max] <=4, "great", [Mail type] = "1st class" && [Delivery time max] <=7, "good", [Mail type] = "1st class" && [Delivery time max] >7, "bad", ... )BInovice you can write a measure like this
Measure = VAR _mail = MAX ( t1[Mail type] ) VAR _time = MAX ( t1[Delivery time] ) VAR rating = CALCULATE ( MAX ( t2[Group] ), FILTER ( t2, _time >= [Delivery time min] && _time <= [Delivery time max] ), TREATAS ( { _mail }, t2[Mail type] ) ) RETURN IF ( ISBLANK ( rating ) = TRUE (), "bad", rating )
3 Replies
BInovice So what are the rules? You could do something like:
Group = SWITCH(TRUE(), [Mail type] = "1st class" && [Delivery time max] <=4, "great", [Mail type] = "1st class" && [Delivery time max] <=7, "good", [Mail type] = "1st class" && [Delivery time max] >7, "bad", ... )Greg_Deckler Thank you very much. It works great. I went for solution from smpa01 as that allows use to update the limits from separated table.
BInovice you can write a measure like this
Measure = VAR _mail = MAX ( t1[Mail type] ) VAR _time = MAX ( t1[Delivery time] ) VAR rating = CALCULATE ( MAX ( t2[Group] ), FILTER ( t2, _time >= [Delivery time min] && _time <= [Delivery time max] ), TREATAS ( { _mail }, t2[Mail type] ) ) RETURN IF ( ISBLANK ( rating ) = TRUE (), "bad", rating )
