cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
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.
tanisha10_0-1596554044336.png

Thanks in Advance

4 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User VII
Super User VII

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

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






Did I answer your question? Mark my post as a solution.

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





View solution in original post

Highlighted
Super User VII
Super User VII

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

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






Did I answer your question? Mark my post as a solution.

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





View solution in original post

Highlighted
Resolver III
Resolver III

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

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.

Please mark it as solution if this resolves your problem.

 

Thanks

View solution in original post

Highlighted
Super User IX
Super User IX

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

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


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

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

13 REPLIES 13
Highlighted
Super User VII
Super User VII

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

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






Did I answer your question? Mark my post as a solution.

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





Highlighted
Super User IX
Super User IX

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

@tanisha10 , 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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

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

@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

Highlighted
Super User VII
Super User VII

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

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






Did I answer your question? Mark my post as a solution.

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





Highlighted
Super User III
Super User III

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

@tanisha10 ,

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?

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
You can follow me on LinkedIn

Please take a quick glance at newly created report:
My Report
Highlighted
Helper III
Helper III

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

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.

 

 

Highlighted
Helper III
Helper III

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

@parry2k

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

Highlighted
Helper III
Helper III

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

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

Highlighted
Super User VII
Super User VII

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

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






Did I answer your question? Mark my post as a solution.

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





View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors