Forum Discussion

BInovice's avatar
BInovice
Frequent Visitor
4 years ago
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 tabl...
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    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's avatar
    smpa01
    4 years ago

    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's avatar
Greg_Deckler
Community Champion
4 years ago

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's avatar
    BInovice
    Frequent Visitor
    4 years ago

    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.