cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

@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

@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

Resolver III
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.

Please mark it as solution if this resolves your problem.

 

Thanks

View solution in original post

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


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

@ me in replies or I'll lose your thread!!!

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
Super User IV
Super User IV

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





Super User IV
Super User IV

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

@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

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





Super User II
Super User II

@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

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.

 

 

@parry2k

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

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

@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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors