Forum Discussion
Show Different Text Values Depending on Hierarchy Level in Matrix Report
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.
Line Status Calc =IF(AND('SoftView'[Missing Material Quantity] = 0, 'SoftView'[Allocatable Quantity 2] = 0), 0,IF('SoftView'[Material] <> "", 1,IF('SoftView'[Date Available 2] = DATE(2500,1,1), 3,IF(AND('SoftView'[Date Available] <> TODAY(), 'SoftView'[Date Available] <> DATE(2500,1,1)), 2))))Status =IF(MAX('Softview'[Line Status Calc]) = 0, "Fully Allocated",IF(MAX('Softview'[Line Status Calc]) = 1, "In Stock",IF(MAX('Softview'[Line Status Calc]) = 2, "On Order",IF(MAX('Softview'[Line Status Calc]) = 3, "On Order - No EDA"))))
Super Usersdsfive , 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.
Line Status Calc =IF(AND('SoftView'[Missing Material Quantity] = 0, 'SoftView'[Allocatable Quantity 2] = 0), 0,IF('SoftView'[Material] <> "", 1,IF('SoftView'[Date Available 2] = DATE(2500,1,1), 3,IF(AND('SoftView'[Date Available] <> TODAY(), 'SoftView'[Date Available] <> DATE(2500,1,1)), 2))))Status =IF(MAX('Softview'[Line Status Calc]) = 0, "Fully Allocated",IF(MAX('Softview'[Line Status Calc]) = 1, "In Stock",IF(MAX('Softview'[Line Status Calc]) = 2, "On Order",IF(MAX('Softview'[Line Status Calc]) = 3, "On Order - No EDA"))))you nailed it. nice job.