topic Re: Group data based on multiple conditions in DAX Commands and Tips

Group data based on multiple conditions

BInovice — Tue, 04 Jan 2022 20:16:33 GMT

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

Re: Group data based on multiple conditions

Greg_Deckler — Tue, 04 Jan 2022 21:18:20 GMT

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

Re: Group data based on multiple conditions

smpa01 — Tue, 04 Jan 2022 21:33:21 GMT

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 )

Re: Group data based on multiple conditions

BInovice — Wed, 05 Jan 2022 17:27:30 GMT

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.