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

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.

14 Replies

  • sumit4732's avatar
    sumit4732
    Advocate II
    9 years ago

    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's avatar
      birdie29
      Advocate II
      9 years ago

      Hi sumit4732

       

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

       

       

      Thank you

      Chris

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

         

         

        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.

         

  • birdie29's avatar
    birdie29
    Advocate II
    9 years ago

    Hi Anonymous

     

    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

    • Anonymous's avatar
      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's avatar
        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