Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I am looking for a DAX help in the below scenario:
Find the no. of teams which has only "Passed" status.
Thanks in Advance
Solved! Go to 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.⚡
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 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.
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 - 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.
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 ,
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?
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.
@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 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.
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.
@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.
@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.
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.
Thanks Perry for your reply. But it is giving me the same results with all the records(Count=5) with "passed" status.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |