Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello
Power BI novice user here. I'm trying to use the calculate(count...) formula to work out the total number of results that are >=5 and <=7 (i.e. all 5's, 6's, 7's). The dataset has '99' as a response (essentially 'Not sure' if converted as 'string') and I want to exclude these as part of the calculation.
Hoping someone can assist.
Thanks
Solved! Go to Solution.
CALCULATE(COUNT('Report1'[Educate employees]),'Report1'[Educate employees]>=5 && 'Report1'[Educate employees]<=7)
the && functions as an AND when turning to SQL, which means the filter has to pass BOTH criteria
Not entirely sure what you mean, but since i'm searching for help myself, i figured maybe i can assist in this.
Have you tried something like:
Calculate(count(ID);[result] >= 5 && result <=7)?
I don't think I understand your response, but I'm unable to make that work...
Can you give me / us a rough sketch of how your table looks?
Can be fictious data.
Educate employees |
4 |
99 |
7 |
5 |
7 |
7 |
7 |
6 |
7 |
this is the formula im using, but doing it this way also includes the '99' which i don't want...
CALCULATE(COUNT('Report1'[Educate employees]),'Report1'[Educate employees]>=5 && 'Report1'[Educate employees]<=7)
the && functions as an AND when turning to SQL, which means the filter has to pass BOTH criteria
Can create a seperate column and do a sum on that.
=IF(AND('Report1'[Educate employees] >= 5;'Report1'[Educate employees] <= 7);1;0)
This will return a value of 1 when its between 5 and 7, and value of 0 when its not, and will do this on each row.
Then you can just SUM() the new column
That works, but for some reason, the result that's coming through isn't correct, so it's like a source data issue. I will keep investigating. Thanks
I think i know why it's not giving me the answer it should be....i think the divide formula is also including the '99s' as part of the total, which I want to exclude from the overall calculation
Hi @aaronvincentnz ,
Is there anything else we can help with regarding this thread?
Best Regards
Rena
Nah I think it's all sorted. Thanks
User | Count |
---|---|
77 | |
76 | |
67 | |
65 | |
47 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
65 |