Dynamically Calculated Values Depending on Hierarchy Level in Matrix Visual

[spart_1337]

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

Table 1

ID	Report	Measure_Type	Item_Type	Lower_Bound	Upper_Bound
1	Report 1	Measure 1	Item 1	0	1
2	Report 1	Measure 1	Item 2	-0.5	0.2
3	Report 1	Measure 2	Item 1	-0.3	0.3
4	Report 1	Measure 2	Item 2	2	4
5	Report 2	Measure 1	Item 1	0	0.5

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

Table 2

ID	Result	Date
1	0.5	13/09/2023
2	0	13/09/2023
3	-0.5	13/09/2023
4	3	13/09/2023
5	-0.3	13/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:

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:

Desired:

Collapsed on Report

Current:

Desired:

Appreciate any guidance.

[devesh_gupta]

@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.

[spart_1337]

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?

[spart_1337 · 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
    ),