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.
I have a matrix report that has a calculated column text value for the lowest hierarchy level called "Line Status"
When I roll up to higher levels I need to have a specific status for the higher level depending on the statuses of the items in the lower level. In the screenshot below, there are two lines with different statuses, and the parent level inherits one of the statsuses, which is not the desired behavior since it is not accurate for the purpose of the report. In this specific case, I need that level to show "Open", not "In Stock" like one of the lower level statuses.
Calculated column formula for determining line status:
Line Status =
IF(AND('SoftView'[Missing Material Quantity] = 0, 'SoftView'[Allocatable Quantity 2] = 0), "Fully Allocated",
IF('SoftView'[Material] <> "", "In Stock",
IF('SoftView'[Date Available 2] = DATE(2500,1,1), "On Order - No EDA",
IF(AND('SoftView'[Date Available] <> TODAY(), 'SoftView'[Date Available] <> DATE(2500,1,1)), "On Order"))))
Solved! Go to Solution.
@sdsfive: I would suggest assigning a numeric value for each status, i.e. 0 = instock all the way to 3 = On-Order NO EDA.
Then at the parent level, you take a MAX([status]), and apply the same logic. So whatever the highest status is, that is the status for the parent item.
That worked very well, thank you! Added an additional measure to use the calculated column numerical values.
@sdsfive , refer if this can help you
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
@sdsfive: I would suggest assigning a numeric value for each status, i.e. 0 = instock all the way to 3 = On-Order NO EDA.
Then at the parent level, you take a MAX([status]), and apply the same logic. So whatever the highest status is, that is the status for the parent item.
That worked very well, thank you! Added an additional measure to use the calculated column numerical values.
you nailed it. nice job.
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 |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |