Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
spart_1337
Frequent Visitor

Dynamically Calculated Values Depending on Hierarchy Level in Matrix Visual

Context
I have a table containing a list of measures for each report, along with lower and upper limits:

Table 1

IDReportMeasure_TypeItem_TypeLower_BoundUpper_Bound
1Report 1Measure 1Item 101
2Report 1Measure 1Item 2-0.50.2
3Report 1Measure 2Item 1-0.30.3
4Report 1Measure 2Item 224
5Report 2Measure 1Item 100.5


I have another table which contains historical records of measure results:

Table 2

IDResultDate
10.513/09/2023
2013/09/2023
3-0.513/09/2023
4313/09/2023
5-0.313/09/2023


If the result falls outside the lower and upper bounds, it displays PASS/FAIL (using a DAX measure which checks whether the result is within the lower and upper bounds) like the following fully expanded matrix:

 

spart_1337_0-1694588239288.png

 

The DAX measure used:

 

 

TEST_Pass_Fail = 
IF(
    AND(
        MAX('test_data'[Result]) >= MAX(test_measures[Lower_Bound]),
        MAX('test_data'[Result]) <= MAX(test_measures[Upper_Bound])
        ),
        "PASS",
        "FAIL"
)

 

 

 

My Question
How can I configure this matrix to dynamically change the PASS/FAIL result based on the current matrix hierarchy level?

For instance,

  • If I collapse on Measure 2, the aggregate result on this hierarchy level should be "FAIL" as not all items passed within Measure 2.
  • If I collapse on Report 1, the aggregate result should be "FAIL" as not all measures within the report passed.

Current output vs Desired output

Collapsed on Measure Type
Current:
image2.png

 

Desired:

spart_1337_1-1694588033927.png

 

Collapsed on Report

Current:
image4.png

 

Desired:

spart_1337_2-1694588143240.png

 

Appreciate any guidance.

1 ACCEPTED SOLUTION

Solved using the following:

 

 

Measure_Result = 
SWITCH(
    TRUE(),
    ISINSCOPE(test_measures[Item_Type]), 
    IF(
        AND(
        MAX('test_data'[Result]) >= MAX(test_measures[Lower_Bound]),
        MAX('test_data'[Result]) <= MAX(test_measures[Upper_Bound])
        ),
        "PASS",
        "FAIL"
    ),
    ISINSCOPE(test_measures[Measure_Type]), IF(MINX('test_measures',[agg_result]) = 0, "FAIL", "PASS"),
    ISINSCOPE(test_measures[Report]), IF(MINX('test_measures',[agg_result]) = 0, "FAIL", "PASS"),
    BLANK()
)

 

 

where the custom measure agg_result is:

 

IF(
        AND(
        MAX('test_data'[Result]) >= MAX(test_measures[Lower_Bound]),
        MAX('test_data'[Result]) <= MAX(test_measures[Upper_Bound])
        ),
        1,
        0
    ),

 

 

View solution in original post

3 REPLIES 3
devesh_gupta
Impactful Individual
Impactful Individual

@spart_1337 You have to segment your meaure very similar to in this video: https://youtu.be/EyL7KMw877Q

 

It will allow you to create a slicer using disconnected tables having values pass & fail.

 

If this solution helps you, please mark it as an accepted solution to help other members find it more easily.

Hi Devesh,

Thanks for your help, I managed to split up my calculations by hierarchy level in the matrix by wrapping the DAX measure in a SWITCH function (see below).

 

 

Measure_Result = 
SWITCH(
    TRUE(),
    ISINSCOPE(test_measures[Item_Type]), 
    IF(
        AND(
        MAX('test_data'[Result]) >= MAX(test_measures[Lower_Bound]),
        MAX('test_data'[Result]) <= MAX(test_measures[Upper_Bound])
        ),
        "PASS",
        "FAIL"
    ),
    ISINSCOPE(test_measures[Measure_Type]), BLANK(),
    ISINSCOPE(test_measures[Report]), BLANK(),
    BLANK()
)

 

I'm now trying to figure out how to replace the two BLANK() values above with an appropriate calculation to achieve my desired result. I'm thinking of using some variation of a CALCULATE() function but not sure how to proceed. Any ideas?

Solved using the following:

 

 

Measure_Result = 
SWITCH(
    TRUE(),
    ISINSCOPE(test_measures[Item_Type]), 
    IF(
        AND(
        MAX('test_data'[Result]) >= MAX(test_measures[Lower_Bound]),
        MAX('test_data'[Result]) <= MAX(test_measures[Upper_Bound])
        ),
        "PASS",
        "FAIL"
    ),
    ISINSCOPE(test_measures[Measure_Type]), IF(MINX('test_measures',[agg_result]) = 0, "FAIL", "PASS"),
    ISINSCOPE(test_measures[Report]), IF(MINX('test_measures',[agg_result]) = 0, "FAIL", "PASS"),
    BLANK()
)

 

 

where the custom measure agg_result is:

 

IF(
        AND(
        MAX('test_data'[Result]) >= MAX(test_measures[Lower_Bound]),
        MAX('test_data'[Result]) <= MAX(test_measures[Upper_Bound])
        ),
        1,
        0
    ),

 

 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors