Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

Anonymous
Not applicable

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

View solution in original post

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

13 REPLIES 13
Anonymous
Not applicable

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

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

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Anonymous
Not applicable

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

@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
Not applicable

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Anonymous
Not applicable

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Anonymous
Not applicable

@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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Anonymous
Not applicable

@parry2k

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.