Forum Discussion

birdie29's avatar
birdie29
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's avatar
    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's avatar
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's avatar
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: