Group data based on multiple conditions

[BInovice]

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

[Greg_Deckler · Accepted solution]

@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",
    ...
  )
    

[smpa01 · Accepted solution]

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

[Greg_Deckler · Accepted solution]

@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]

@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.