Need help on DAX code for Pass result based on measurement specification

[mahenkj2]

Hi,

I am preparing a report for measurement data, one product is measured for several tests and each result is stored in a new row. So its a fact table. Another table is connected with it as one to many containing details on the tests done and specification, this is dimension table. I am trying to write two measures:

1. calculating Pass/Fail for individual test for each product
2. another measure to calculate If all such individual tests for a product are passed or not. So a final pass needs to be calculated.

Record ID	Product ID	ID_TestName	Measurement Value	Lower spec	Upper spec	Result	Final result
1	A	1	0.32	0.2	0.4	Pass	Fail
2	A	2	1310	1250	1350	Pass	Fail
3	A	3	200	200		Pass	Fail
4	A	4	5	0	10	Pass	Fail
5	A	5	3	3	5	Pass	Fail
6	A	6	11.432	11	12	Pass	Fail
7	A	7		13	16	Fail	Fail
8	B	1	0.21	0.2	0.4	Pass	Pass
9	B	2	1290	1250	1350	Pass	Pass
10	B	3	190	200		Pass	Pass
11	C	1	0.3177	0.2	0.4	Pass	Fail
12	C	2	1325	1250	1350	Pass	Fail
13	C	3	225	200		Fail	Fail
14	C	4	5.4	0	10	Pass	Fail
15	C	5	3.9	3	5	Pass	Fail
16	C	6	11.1	11	12	Pass	Fail
17	C	7	14.7	13	16	Pass	Fail

Above table is the fact table, I have brough the spec detail also here using measures and relationship. Now last two column needs to be written.

I have so far written:

Result = if(and(

AVERAGE('TestMeasurement'[Measured Value])>AVERAGE('SpecTable$'[Lower Spec]),

AVERAGE('TestMeasurement'[Measured Value])<AVERAGE('SpecTable'[UPPer Spec])),

"Pass","Fail")

I want to avoid Average/Sum in this formula, but I am not sure how that can be made possible. Second,

this does not calculate correct when test is not measured and spec is -ve to +ve e.g. -1 to 1, then consider

non meaured value as 0 and shows pass, but I would like to show here fail.

Second measure,

ResultPass = CALCULATE( COUNTROWS(FILTER('TestMeasurement','Measures'[Result]="Pass"))+0)

It does not work, I need to count all Pas value, so evaluation conext should perhaps remove the filter

on ID_TestName, but I am unable to do that.

Please advise and let me know if any questions.

[amitchandak]

@mahenkj2 , refer

 Measure 
 Result = if(and(
AVERAGE('TestMeasurement'[Measured Value])>AVERAGE('SpecTable$'[Lower Spec]),
AVERAGE('TestMeasurement'[Measured Value])<AVERAGE('SpecTable'[UPPer Spec])),
"Pass","Fail")

column 
 Result column = if(and(
'TestMeasurement'[Measured Value]>'SpecTable$'[Lower Spec],
'TestMeasurement'[Measured Value]<'SpecTable'[UPPer Spec]),
"Pass","Fail")


 Measure 
Final result  = maxx(summarize('TestMeasurement','TestMeasurement'[Product ID], "_1",count('TestMeasurement'[Recort ID]) ,  "_2" , countx('TestMeasurement', if(and(
'TestMeasurement'[Measured Value]>'SpecTable$'[Lower Spec],
'TestMeasurement'[Measured Value]<'SpecTable'[UPPer Spec]),
[Recort ID],blank()))),if([_1] =[_2] = "Pass","Fail"))


 Measure 

[Greg_Deckler · Accepted solution]

@mahenkj2 - For the second issue, that is a measures aggregation issue. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

For the first issue, you cannot avoid the AVERAGE statements because you are doing this in a measure and measures require you to wrap column references in some aggregation like SUM, AVERAGE, MIN, MAX, etc. I'm not 100% clear on this requirement what you are going for. Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[mahenkj2]

@Greg_DecklerThanks. After looking at your suggestion, I understood that Summarize should be the right function useful in my case, as you point in your linked post.

I could get access to this work with little delay and had to iterate several things then on, finally I managed well.

Just one request, your post points to a direction, and I saw people benefitted from that but I think you can improvise it with little mode detailing and pasting table image etc instead of writing relationships in text. For developer, this language may look no problem, but for newbies like me will struggle to go through it in one go.

Thanks.

[Greg_Deckler]

@mahenkj2 - Glad you got it! Thanks for the suggestion!