Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 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 )
@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 )
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
32 | |
30 | |
18 | |
18 |