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.
Hello,
I am looking for a way to create a calculated column that matches the Date / Category and Value against set bands in the Category Bands with the Effective Date table. Currently, I am using Filter and IF statements to get the desired result but were hoping to find a more elegant solution.
Could anyone please advice me on the best approach?
Category Values
Date | Category | Value |
02/01/2021 | A | 9 |
03/01/2021 | B | 400 |
04/01/2021 | C | 100 |
05/01/2021 | D | 9 |
25/02/2021 | A | 60 |
25/02/2021 | B | 100 |
25/02/2021 | C | 30 |
25/02/2021 | A | 10 |
Category Bands with Effective Dates
Category | 1st Class | 2nd Class | 3rd Class | Effective Date |
A | >100 | 100-50 | <50 | 01/01/2021 |
B | >200 | 200-70 | <70 | 01/01/2021 |
C | >50 | 50-10 | <10 | 01/01/2021 |
D | >10 | 10-5 | <5 | 01/01/2021 |
A | >50 | 50-10 | <10 | 20/02/2021 |
B | >10 | 10-5 | <5 | 20/02/2021 |
D | >10 | 10-5 | <5 | 20/02/2021 |
Desired Outcome
Date | Category | Value | Class |
02/01/2021 | A | 9 | 3rd |
03/01/2021 | B | 400 | 1st |
04/01/2021 | C | 100 | 1st |
05/01/2021 | D | 9 | 2nd |
25/02/2021 | A | 60 | 1st |
25/02/2021 | B | 100 | 2nd |
25/02/2021 | C | 30 | 2nd |
25/02/2021 | A | 10 | 2nd |
Solved! Go to Solution.
Hi @Anonymous ,
Please refer this formula.
Column =
VAR _Effective =
CALCULATE (
MAX ( 'Category Bands'[Effective Date] ),
FILTER (
'Category Bands',
'Category Bands'[Category] = 'Category Values'[Category]
&& 'Category Bands'[Effective Date] <= 'Category Values'[Date]
)
)
VAR _1st =
CALCULATE (
MAX ( 'Category Bands'[1st Class] ),
FILTER (
'Category Bands',
'Category Bands'[Category] = 'Category Values'[Category]
&& 'Category Bands'[Effective Date] = _Effective
)
)
VAR _max =
MID ( _1st, 2, 9999 ) + 0
VAR _3rd =
CALCULATE (
MAX ( 'Category Bands'[3rd Class] ),
FILTER (
'Category Bands',
'Category Bands'[Category] = 'Category Values'[Category]
&& 'Category Bands'[Effective Date] = _Effective
)
)
VAR _min =
MID ( _3rd, 2, 9999 ) + 0
RETURN
SWITCH (
TRUE (),
'Category Values'[Value] > _max, "1st",
'Category Values'[Value] < _min, "3rd",
"2nd"
)
Best Regards,
Jay
Hi @Anonymous ,
Please refer this formula.
Column =
VAR _Effective =
CALCULATE (
MAX ( 'Category Bands'[Effective Date] ),
FILTER (
'Category Bands',
'Category Bands'[Category] = 'Category Values'[Category]
&& 'Category Bands'[Effective Date] <= 'Category Values'[Date]
)
)
VAR _1st =
CALCULATE (
MAX ( 'Category Bands'[1st Class] ),
FILTER (
'Category Bands',
'Category Bands'[Category] = 'Category Values'[Category]
&& 'Category Bands'[Effective Date] = _Effective
)
)
VAR _max =
MID ( _1st, 2, 9999 ) + 0
VAR _3rd =
CALCULATE (
MAX ( 'Category Bands'[3rd Class] ),
FILTER (
'Category Bands',
'Category Bands'[Category] = 'Category Values'[Category]
&& 'Category Bands'[Effective Date] = _Effective
)
)
VAR _min =
MID ( _3rd, 2, 9999 ) + 0
RETURN
SWITCH (
TRUE (),
'Category Values'[Value] > _max, "1st",
'Category Values'[Value] < _min, "3rd",
"2nd"
)
Best Regards,
Jay
Hi,
I suggest having a category table like the below structure.
Please check the below picture and the attached pbix file.
It is for creating a new column.
Class CC =
VAR currentcategory = Data[Category]
VAR currentdate = Data[Date]
VAR effectivecategorydate =
MAXX (
FILTER (
Category,
Category[Category] = currentcategory
&& Category[Effective Date] <= currentdate
),
Category[Effective Date]
)
VAR effectivecategorytable =
FILTER (
Category,
Category[Category] = currentcategory
&& Category[Effective Date] = effectivecategorydate
&& Data[Value] >= Category[Min]
&& Data[Value] <= Category[Max]
)
RETURN
MAXX ( effectivecategorytable, Category[Class] )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Great solution, yet I am unable to change the structure of the category table as it has thousands of rows.
Instead I came up with the solution below:
1. Created Effectiveness End date in the "Category Bands with Effective Dates" table.
2. Added this column to "Category Values".
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |