cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Show Different Text Values Depending on Hierarchy Level in Matrix Report

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.

 

2020-05-29_12-30-43.png

 

 

 

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"))))

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Helper V
Helper V

Re: 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.

View solution in original post

Highlighted
Frequent Visitor

Re: Show Different Text Values Depending on Hierarchy Level in Matrix Report

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"))))

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Show Different Text Values Depending on Hierarchy Level in Matrix Report

@sdsfive , refer if this can help you

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper V
Helper V

Re: 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.

View solution in original post

Highlighted
Frequent Visitor

Re: Show Different Text Values Depending on Hierarchy Level in Matrix Report

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"))))

View solution in original post

Highlighted
Helper V
Helper V

Re: Show Different Text Values Depending on Hierarchy Level in Matrix Report

you nailed it. nice job.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors