cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
birdie29 Regular Visitor
Regular Visitor

ALL EXCEPT Help

Hi There

 

The following column should return 'Yes' if all the 'Engaged Answer' rows state '1' for a particular 'Worker' however as you can see below this isn't the case:

 

All Except.PNG

 

I think the issue is highlighted by having a filter on which only shows 'Q#' 1-12 because if I select all 'Q#'s then it is correct that the 'Fully Engaged' column should show 'No'.

 

Please can someone help me?

 

Thank you

Chris

1 ACCEPTED SOLUTION

Accepted Solutions
ValubiMartin Senior Member
Senior Member

Re: ALL EXCEPT Help

@birdie29 Sorry for the late answer.

 

Alright. This DAX formula should give you the desired result.

 

Percentage of engaged workers = IF(CALCULATE(COUNT(Test1[Engaged answer]);Test1[Engaged answer]=1)/CALCULATE(COUNT(Test1[Engaged answer]))=BLANK();0;CALCULATE(COUNT(Test1[Engaged answer]);Test1[Engaged answer]=1)/CALCULATE(COUNT(Test1[Engaged answer])))

 

Let me know if it works out for you.

View solution in original post

14 REPLIES 14
sumit4732 Regular Visitor
Regular Visitor

Re: ALL EXCEPT Help

Hi @birdie29,

 

Please try using sum in place of min.

If it doesnt help please share a sample dataset, so we can help you better.

 

-Sumit

birdie29 Regular Visitor
Regular Visitor

Re: ALL EXCEPT Help

Hi @sumit4732

 

Thanks for your suggestion however it did not work. Please find an example dataset below:

 

All Except 2.PNG

 

Thank you

Chris

ValubiMartin Senior Member
Senior Member

Re: ALL EXCEPT Help

 

 

Hi @birdie29

 

I come with a solution. Please try below DAX formula:

 

Fully engaged =

IF(CALCULATE(SUM(Test1[Engaged answer]);FILTER(ALLEXCEPT(Test1;Test1[Worker]);CALCULATE(SUM(Test1[Engaged answer]);VALUES(Test1[Worker]))))=22;"Yes";"No")

 

Let me know how it goes.

 

Best,

Martin

 

EDIT: Picture below. Ignore the test table. Focus on Test1 table.

 

Fully engaged calc.png

birdie29 Regular Visitor
Regular Visitor

Re: ALL EXCEPT Help

Hi @ValubiMartin

 

That's a really good suggestion however we also need to know if the 'Worker' is 'Fully Engaged' based on only #Q's 1-12 also, however if I use your solution the score in this case would not come to 22 but only 12 and therefore become 'No' under the 'Fully Engaged' column. 

 

Is there another solution you can think of?

 

Thank you

Chris

Highlighted
ValubiMartin Senior Member
Senior Member

Re: ALL EXCEPT Help

@birdie29

 

Whoops I missed that part. Given your information this should work.

 

Fully engaged = IF(CALCULATE(SUM(Test1[Engaged answer]);VALUES(Test1[Worker]);Test1[Q#]<=12)=12;"Yes";"No")

 

Does this solve it?

 

birdie29 Regular Visitor
Regular Visitor

Re: ALL EXCEPT Help

Hi @ValubiMartin

 

Sorry I'm not being very clear. The 'Fully Engaged' response needs to be dynamic ie the user may select just #Q 1, 5 and 7 in which case if the 'Engaged Answer' for just these questions is '1' then I would want the formula to state 'Yes' regardless of what the response is for all the other questions.

 

Does that make sense?

 

Thank you

Chris

ValubiMartin Senior Member
Senior Member

Re: ALL EXCEPT Help

@birdie29

 

It makes perfect sense. Sorry for not picking it up earlier. I spend some time to come up with this.

 

Fully Engaged =

IF(CALCULATE(SUM(Test1[Engaged answer]);VALUES(Test1[Worker]);FILTER(ALLEXCEPT(Test1;Test1[Q#]);CALCULATE(COUNTROWS(Test1);Test1[Engaged answer]<>0)))=COUNTROWS(Test1);"Yes";"No")

 

Try it out and see if it has the right dynamic. If it solves your issue don't forget to give kudos and accept a solution.

birdie29 Regular Visitor
Regular Visitor

Re: ALL EXCEPT Help

Hi @ValubiMartin

 

Thank you for spending so much time on this, I really appreciate it.

 

Unfortunately I'm getting an error message, please see below:

 

All Except 3.PNG

ValubiMartin Senior Member
Senior Member

Re: ALL EXCEPT Help

@birdie29

 

Don't worry about it. I love to help out.

 

Are you trying to create it as a column? Try creating it as a measure and see if it works out!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 309 members 3,281 guests
Please welcome our newest community members: