Forum Discussion

Advocate II

9 years ago

Solved

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: I think the i...

Need Help

Anonymous
9 years ago
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.

Anonymous

Not applicable

9 years ago

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

Advocate II

9 years ago

Hi Anonymous

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

Anonymous
Not applicable
9 years ago
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
  Advocate II
  9 years ago
  HiÂ Anonymous
  
  Thank you for spending so much time on this, I really appreciate it.
  
  Unfortunately I'm getting an error message, please see below:
  - Anonymous
    Not applicable
    9 years ago
    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!