Forum Discussion
Static Grouping in-between value
Hi All,
I'm having a bit of trouble grasping static grouping in DAX. I have 2 tables
1) Age grouping
From To Age group
| -1 | 0 | A (0) |
| 1 | 2 | B (1-2) |
| 3 | 5 | C (3-5) |
| 6 | 10 | D (6-10) |
| 11 | 20 | E (11-20) |
| 21 | 30 | F (21-30) |
| 31 | 50 | G (31-50) |
| 51 | 100 | H (51-100) |
| 101 | 999 | I (100+) |
And a 2) Tickets:
| COMPANY | STATUS | TICKET_ID | CREATION_DATE | TICKET_AGE |
| AT | Working | 970248 | 07/06/2021 | 497 |
| AT | Open | 848250 | 07/09/2022 | 40 |
| AT | Open | 993870 | 19/09/2022 | 28 |
| AT | Open | 701673 | 27/09/2022 | 20 |
| AT | Open | 700697 | 27/09/2022 | 20 |
My objetive is to, in table 2) "Tickets" create a new column called "Age group" .
This new column is populated with the text in Age Group of Table 1 "Age grouping" by comparing of the value in TICKET_AGE agaisnt "From" and "to" of Table 1 "Age grouping".
How can I go about doing this?
Your insight is much appreciated!
Try
Age group = VAR ReferenceAge = 'Table 2'[Age] RETURN SELECTCOLUMNS ( FILTER ( 'Table 1', 'Table 1'[From] <= ReferenceAge && 'Table 1'[To] >= ReferenceAge ), "@val", 'Table 1'[Age group] )
2 Replies
johnt75Super User
Try
Age group = VAR ReferenceAge = 'Table 2'[Age] RETURN SELECTCOLUMNS ( FILTER ( 'Table 1', 'Table 1'[From] <= ReferenceAge && 'Table 1'[To] >= ReferenceAge ), "@val", 'Table 1'[Age group] )