Power BI DAX : multi-column and multi-row condition and group by 2 columns

Question

Folks, I am trying to create a calculated column/measures and experiencing issues.

My Data-set looks like this:

City	Building Name	Test Date	Component	Test Result	Calculated Result
-	-	-	-	-	-
City1	Build1	1/3/2014	Component A	Pass	None
City1	Build1	1/11/2014	Component 1	Fail	Fail1
City1	Build1	1/11/2014	Component 2	Pass	Fail1
City1	Build1	1/11/2014	Component 3	Pass	Fail1
City1	Build1	1/06/2014	Component A	Fail	MultiFail
City1	Build1	1/06/2014	Component 1	Fail	MultiFail
City1	Build1	1/06/2014	Component 2	Pass	MultiFail
City1	Build1	1/06/2014	Component 3	Fail	MultiFail

I am looking at Component & Test Result columns, count list of Fails - grouped by Building Name and Date; then generate Calculated result depending on the number of components failed.

If Single component Test Result = fail - then Calculated Result = Fail1 If CountA(components Test Result = fail) <=2 then Calculated Result = Fail2 If CountA(components Test Result = fail) > 2 then Calculated Result = MultiFail If Component1 AND ComponentA Test Result = Fail then Calculated Result = FailMail

So far, I tried various ways in solving this with a step ahead and 2 steps behind:

I created a calculated column to count # Fails to be used for Calculated Result and struggling to generate Calculated Result.

Tests_Failed = CALCULATE(COUNT(Table[TestResult]),FILTER(Table,Table[date]=MAX(Table[date]) && Table[TestResult]="Fail"))

Another way I tried approaching the problem

Calculated Result =
VAR Component = Table[Component] VAR Date1 = Table[Test date] VAR Build = Table[Building Name] RETURN CALCULATE(DISTINCTCOUNT('Table'[Component]), ALL(Table), FILTER('Table', 'Table'[Test Result]="Fail" && 'Table'[date] = Date1 && 'Table'[Building Name]=Build)))

smpa01 smpa01 · Accepted Answer · 2021-06-30T20:19:45

Can you try this

Measure =
VAR _1 =
    CALCULATE (
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Component] ),
            FILTER ( 'Table', 'Table'[Test Result] = "Fail" )
        ),
        ALLEXCEPT ( 'Table', 'Table'[Building Name], 'Table'[Test Date] )
    )
VAR _2 =
    SWITCH (
        TRUE (),
        ISBLANK ( _1 ) = TRUE (), "None",
        _1 = 1, "Fail1",
        "MultiFail"
    )
RETURN
    _2

Power BI DAX : multi-column and multi-row condition and group by 2 columns

1 Answers