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

[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

[Tahreem24]

@Anonymous ,

COuld you please clear my doubt? As per your data Status=Passed is present at 5 different teams so how could your count come as 2?

[Anonymous]

Teams which has only passed records, no failed records is my requirement. Now you can see Team "A" has both Passed and Failed status. So I am not considering Team A in my result. Only Team "B" and Team "E" has only passed records.

[amitchandak]

@Anonymous , Try like

countX(filter(summarize('Summerize table', 'Summerize table'[Team],"_1", count('Summerize table'[Team]), "_2",CALCULATE(count('Summerize table'[Team]),'Summerize table'[Status]="Passed")) , [_1] =[_2]),[Team])

[Anonymous]

@amitchandak Thanks Amit, But It is giving me blank().

[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.⚡

[Anonymous]

@parry2k

Thank you so much Perry for your response.

But it is giving me 1 for different scenarios i tested, as distinct count for "Passed" and "Failed" status will be always 1.

Just wanted to understand how this "except" function is working in the return statement?

[Greg_Deckler · Accepted solution]

@Anonymous - As a variation on @parry2k I think you could just do this:

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

Basically the same was @parry2k . You get all of the team id's that have a status of passed anywhere. Same for failed. So, when you use EXCEPT, you filter out any teams that have a failed. Thus, you are left with only teams that have never failed and you count them.

[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.⚡

[parry2k]

@Anonymous add following measure

Success count = CALCULATE(DISTINCTCOUNT('Summerize table'[Team]),'Summerize table'[Status]="Passed" )

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]

@parry2k 

This is considering the teams with failed status also, which i dont want.

It should consider only teams which has only Passed record, no failed records

[parry2k]

@Anonymous that should be super easy

Count = 
VAR __teams = CALCULATETABLE ( VALUES ( Table[Team] ), Table[Status] = "Passed" )
RETURN
COUNTROWS ( __teams )

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]

@parry2k

Thanks Perry for your reply. But it is giving me the same results with all the records(Count=5) with "passed" status.