How to write DAX to find count of teams with only passed status?

[Anonymous]

Hi All,

I am looking for a DAX help in the below scenario:

Find the no. of teams which has only "Passed" status.

Success count = CALCULATE(DISTINCTCOUNT('Summerize table'[Team]),FILTER('Summerize table','Summerize table'[Status]="Passed" && 'Summerize table'[Status]<>"Failed" ) )

Which is giving total count=5, It should be 2.

Thanks in Advance

[parry2k · Accepted solution]

@Anonymous sorry I misread the question, here is the measure

Measure = 
VAR __passedTeam = CALCULATETABLE ( VALUES ( 'Table'[Team] ), 'Table'[Status] = "Passed" )
VAR __failedTeam = CALCULATETABLE ( VALUES ( 'Table'[Team] ), 'Table'[Status] = "Failed" )
RETURN
COUNTROWS ( EXCEPT ( __passedTeam, __failedTeam ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Anonymous it is not clear from your reply if your problem is solved or not? Can you please clarify?

Read more about EXCEPT here.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous · Accepted solution]

Hi @Anonymous  Please try this measure.

Measure =
VAR Passed =
    FILTER ( Table1, Table1[Status] = "Passed" )
VAR Result =
    SUMX (
        Passed,
        VAR Team = [Team]
        RETURN
            IF (
                COUNTROWS (
                    FILTER ( Table1, Table1[Status] = "Failed" && Table1[Team] = Team )
                ) >= 1,
                0,
                1
            )
    )
RETURN
    Result

Appreiate with kudos.

Please mark it as solution if this resolves your problem.

Thanks