March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
@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.
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
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?
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
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.
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:
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!
Hi @Anonymous
Yes I was trying to work it as a column but now it's a measure it works!
What I now want to do with this measure is have a calculation that shows me the percentage of workers who are 'Fully Engaged' "Yes". ie if I select #Q 1-7 what percentage of the workers answered "Yes" to all of them?
I'm sorry for all the questions but in solving my question it's made me think more about what I want to do!
Thank you once again
Chris
@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.
Hi @Anonymous
Sorry for the late response, I did try it but I didn't get what I expected so I'm just working through it to understand it more first before coming back to you. Thanks for following up!
Hi @birdie29,
Have you managed to solve this issue? If yes, could you accept helpful replies as solution to close this thread?
If you still have any question on this issue, feel free to post here.
Regards
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
Hi @sumit4732
Thanks for your suggestion however it did not work. Please find an example dataset below:
Thank you
Chris
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
148 | |
92 | |
72 | |
58 |