Forum Discussion
Helper IICalculated Measure is causing slicer to be ignored
Hey Guys,
I have a simple measure that calculates Quality Percentage (Passed Reviews / (Passed Reviews + Failed Reviews)) by employee. On my report I also have a slicer that filters the employees by office. The calc works fine by itself, but when I try to get a little more intricate, by changing the quality % to 100% when there are no reviews, then the slicer doesnt work anymore. Instead the table shows all employees and assigns everyone 100% with the exception of the employees that are in the slicer office selection. Their calc works as it should. How can I adjust my calculation so that the slicer continues to work and only show the employees for the office selected?
Here's the current calc I'm using.....
zeke101 , Try
QualityQA% = if( isblank(CALCULATE( sum('Quality Data'[Pass])/(sum('Quality Data'[Fail])+sum('Quality Data'[Pass])))) , 1, CALCULATE( sum('Quality Data'[Pass])/(sum('Quality Data'[Fail])+sum('Quality Data'[Pass]) ))) ////////Or QualityQA% = if( CALCULATE( sum('Quality Data'[Pass])/(sum('Quality Data'[Fail])+sum('Quality Data'[Pass]))) == BLANK(), 1, CALCULATE( sum('Quality Data'[Pass])/(sum('Quality Data'[Fail])+sum('Quality Data'[Pass]) )))
6 Replies
- lbendlin
Super User
CALCULATE() does a context transition. You need to decide which part of your old context you want to bring over into the new world, with ALLEXCEPT, or KEEPFILTERS, or similar.
- zeke101
Hi lbendlin
I'm not sure how to work this in as I want to the table to be dynamic in updating to show employees from their respective office only. Perhaps I do not need the "Calculate" measure, but even a basic formula results in the table showing all employees.....By that I mean this formula where I just removed the calculate....
QualityQA% =if(isblank(sum('Quality Data'[Pass])/(sum('Quality Data'[Fail])+sum('Quality Data'[Pass]))) ,1,sum('Quality Data'[Pass])/(sum('Quality Data'[Fail])+sum('Quality Data'[Pass])))
- amitchandak
zeke101 , Try
QualityQA% = if( isblank(CALCULATE( sum('Quality Data'[Pass])/(sum('Quality Data'[Fail])+sum('Quality Data'[Pass])))) , 1, CALCULATE( sum('Quality Data'[Pass])/(sum('Quality Data'[Fail])+sum('Quality Data'[Pass]) ))) ////////Or QualityQA% = if( CALCULATE( sum('Quality Data'[Pass])/(sum('Quality Data'[Fail])+sum('Quality Data'[Pass]))) == BLANK(), 1, CALCULATE( sum('Quality Data'[Pass])/(sum('Quality Data'[Fail])+sum('Quality Data'[Pass]) )))- zeke101
Hi amitchandak
Unfortunately both of these solutions still have the table ignoring the Office Slicer and continues to show all employees (instead of just the employees assigned to that Office). Something else I noticed, when I added the "Office" to the table it appears to be attaching the office name to all the employees as well.....See screenshot: everyone above the yellow line is in Chicago while everyone below is not.
- zeke101
Your calculation along with a change to table relationships allowed for the calc data to appear. I'm still a little fuzzy on Cross Filter direction, but when I made a change from "Both" to "Single" everything reappeared.
- v-diye-msft
Community Support
Hi zeke101
If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!
