cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bdmichael09 Regular Visitor
Regular Visitor

Power BI Filtering issue caused by If statement measures

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:

Full Matrix

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:

Filtered using slicer

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.

1 ACCEPTED SOLUTION

Accepted Solutions
bdmichael09 Regular Visitor
Regular Visitor

Re: Power BI Filtering issue caused by If statement measures

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!

View solution in original post

1 REPLY 1
bdmichael09 Regular Visitor
Regular Visitor

Re: Power BI Filtering issue caused by If statement measures

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!

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 3,982 guests
Please welcome our newest community members: