Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Apologies up front, this is going to take a second to explain but I'd rather be thorough so the problem is clear. The main problem that I am having is that people are not being properly filtered out of a matrix that I am using and I believe I have isolated the problem to some measures that I have written. Below is a picture of what I'm working with:
My problem is that when I try to apply a filter using the slicer, you would expect the available names to be filtered out of the matrix based on if they fall under the particular team leader or director selected. However, they do not, as seen in this picture:
Clearly, people are not being filtered out because there are still values in the AnswerRatePass column and the SurveyScorePass column. I will focus on just AnswerRatePass to cut the problem in half. The purpose of AnswerRatePass is to output text to identify if the Answer Rate of a person exceeded their goal, met the goal, or missed the goal. My code for AnswerRatePass is:
AnswerRatePass = if([AnswerRateGoal] = 1,"Exceed", if([AnswerRateGoal]=0,"Achieved","Missed"))
As you surely noticed, [AnswerRatePass] is using the value of [AnswerRateGoal] to determine what to do. I use this because it sort of simplifies the code for my Recommendation measure, which assesses if a person met their goals for both criteria, meaning we suggest to promote this person, or what their case is. My code for AnswerRateGoal is:
AnswerRateGoal = if(ISBLANK([AnswerRate]),0, if(values(TMD[Title])="Executive", if([AnswerRate]>=.94,1, if([AnswerRate]>=.92,0,-1)), if(values(TMD[Title])="President's Club", if([AnswerRate]>=.96,1, if([AnswerRate]>=.94,0,-1)), if([AnswerRate]>=.96,0,-1) )))
If these measures (AnswerRatePass, SurveyScorePass, and Recommendation) are removed from the matrix, the slicers work exactly as expected. So, long story short, what I'm pretty sure I need is some sort of filter for these measures so that these values will disappear when using a slicer that doesn't apply to that person, allowing the individual person to also be filtered out by the slicer as well. Unless someone has a different idea, I'm pretty sure that's what I need but I haven't been able to come up with a way to do it. I'm open to any help/suggestions.
Solved! Go to Solution.
If anyone else finds this and it helps, I got an answer to the question. Adding if(isblank([AnswerRate]),Blank(), to my code solved the problem. To make the code more efficient though, I got some advice to use Switch rather than nested if statements which is SO much simpler and makes a ton of sense. My new code is
AnswerRatePass = if(isblank([AnswerRate]),BLANK(),
switch([AnswerRateGoal],1,"Exceed", 0,"Achieved","Missed"))
I hope this helps someone!
If anyone else finds this and it helps, I got an answer to the question. Adding if(isblank([AnswerRate]),Blank(), to my code solved the problem. To make the code more efficient though, I got some advice to use Switch rather than nested if statements which is SO much simpler and makes a ton of sense. My new code is
AnswerRatePass = if(isblank([AnswerRate]),BLANK(),
switch([AnswerRateGoal],1,"Exceed", 0,"Achieved","Missed"))
I hope this helps someone!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |