cancel
Showing results for
Did you mean:
Highlighted
Helper III

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

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.

13 REPLIES 13
Highlighted
Helper III

@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?

Highlighted
Super User IV

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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Resolver III

Hi @tanisha10  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.

Thanks

Highlighted
Super User IV

@tanisha10 - 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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors