obtain specific value based on multiple conditions

[Anonymous]

Hi all,

I have the following dataset

Customer	Type	Subtype	Monitoring		Result
AAA	X	1	INTERNAL		OK
AAA	Y	2	EXTERNAL		KO
AAA	X	3	EXTERNAL		OK
BBB	X	1	INTERNAL		OK
BBB	Y	1	EXTERNAL		OK
BBB	Y	2	INTERNAL		OK
CCC	X	2	INTERNAL		OK
CCC	Y	3	EXTERNAL		OK
CCC	Y	3	INTERNAL		KO
CCC	Y	3	INTERNAL		KO

I need to create a Measure to obtain:

• -1 if one or more Monitoring are EXTERNAL and are KO
• 0 if one or more Monitoring are INTERNAL and are KO
• 1 if I have all OK

So in the sample Data exposed above, for Customer AAA I need my Measure displays -1 because one row has EXTERNAL and KO, for Customer BBB I want 1 because I have all OK, and for customer CCC I need 0 because I have more than one INTERNAL in KO (but would have been sufficient only one KO).

Thanj you a lot!!!

[Ashish_Mathur]

Hi,

Wouldn't you want a calculated column formula solution instead? 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Measure = 
VAR _SUMMARIZE = SUMMARIZE('Table','Table'[Customer],'Table'[Monitoring],'Table'[Result],"Combine",COMBINEVALUES(" ",'Table'[Monitoring],'Table'[Result]))
VAR _VALUECOMBINE = SUMMARIZE(FILTER(_SUMMARIZE,[Customer] = MAX('Table'[Customer])),[Combine])
VAR _COUNTROW = CALCULATE(COUNT('Table'[Customer]))
VAR _COUNTRESULT = CALCULATE(DISTINCTCOUNT('Table'[Result]))
RETURN
SWITCH(TRUE(),_COUNTRESULT=1&&"OK" IN VALUES('Table'[Result]),1,"INTERNAL KO" IN _VALUECOMBINE,0,"EXTERNAL KO" IN _VALUECOMBINE,-1)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.