The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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,
Current output vs Desired output
Collapsed on Measure Type
Current:
Desired:
Collapsed on Report
Current:
Desired:
Appreciate any guidance.
Solved! Go to 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
),
@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
),