cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Switch & Calculate Issue

Hi Folks,

 

I'm trying to set up a performance type scenario. What I want to do is this:

 

If they received between a 3 or 3.5 in FY16 and FY17 then they are a core performer. If they received a 4 or higher in FY16 and FY17 then they are a high performer. If they received less than a 3 in FY16 and FY17  then I want to say they are a low performer. 

 

Some logic I have written is this: 

 

Performance Type = Switch (
True(),
Calculate ( Count ('Rating-FY17'[FY17 Rating]),'Rating-FY17'[FY17 Rating] >= 3) &&
Calculate ( Count ('Rating-FY17'[FY17 Rating]),'Rating-FY17'[FY17 Rating] <= 3.5) &&
Calculate ( Count ('Rating-FY16'[Overall Performance Rating]),'Rating-FY16'[Overall Performance Rating] >= 3) &&
Calculate ( Count ('Rating-FY16'[Overall Performance Rating]),'Rating-FY16'[Overall Performance Rating] <= 3.5), "Core Performer",
Calculate ( Count ('Rating-FY17'[FY17 Rating]),'Rating-FY17'[FY17 Rating] < 3) &&
Calculate ( Count ('Rating-FY16'[Overall Performance Rating]),'Rating-FY16'[Overall Performance Rating] < 3), "Low Performer",
Calculate ( Count ('Rating-FY17'[FY17 Rating]),'Rating-FY17'[FY17 Rating] >= 4) &&
Calculate ( Count ('Rating-FY16'[Overall Performance Rating]),'Rating-FY16'[Overall Performance Rating] >= 4), "High Performer",
"N/A"
)

 

However, in looking at the data, it doesn't seem to be calculating correctly for "Low Performers" it's not picking up those have had < 3 for the last 2 years for whatever reason. 

 

Additionally, I feel like some sort of average needs to be rolled in here, too, because, what if they received a 3 in FY16 and a 4 in FY17, the average would then be a 3.5 and would be a Core Performer rather than N/A. What can I do for this? Any help would be great please. 

 

The healthcare company I work for is super strict with performance but we need to identify this easily. Any help would be much appreciated!

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Switch & Calculate Issue

You just have to work this until you get your logic correct.

 

For example, by your stated rules, Donald Duck gets an N/A because his FY17 score is NOT less than 3.

 

Performance? = 
VAR Rating = SWITCH(
TRUE(),
[FY16]>=4 && [FY17]>=4,"High Performer", 
([FY16]>=3 && [FY16] <= 3.5) && ([FY17] >= 3 && [FY17]<=3.5),"Core Performer",
[FY16] < 3 && [FY17] < 3,"Low Performer",
"N/A"
)
RETURN Rating

For your second one, maybe something like this:

 

Performance2 = 
VAR Score = [FY17]+[FY16]/2
VAR Rating = SWITCH(
                    TRUE(),
                    Score>=4,"High Performer",                    
                    Score>=3 && Score <= 3.5,"Core Performer",
                    Score < 3,"Low Performer",
                    "N/A"
                )
RETURN Rating

These formulas work for their defined rules, but you will have to edit in your own rules.

 

 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
Highlighted
Resolver I
Resolver I

Re: Switch & Calculate Issue

Anyone? @Greg_Deckler

Highlighted
Super User IV
Super User IV

Re: Switch & Calculate Issue

You didn't post sample data so I created some:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTVfSUTIB41idaCWnzJycSgWn/CSgiBEYg0S9MnOT8oE8Yz1TEAkWi8osKKgE88B6YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t, FY17 = _t, FY16 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"FY17", type number}, {"FY16", type number}})
in
    #"Changed Type"

I then creaed this measure, which I think is simpler than the one you have and also solves the problem of average score.

 

Rating = 
VAR FY17 = SUM(Performance[FY17])
VAR FY16 = SUM(Performance[FY16])
VAR Score = FY17+FY16
VAR Rating = SWITCH(
                    TRUE(),
                    Score>=6 && Score <= 7,"Core Performer",
                    Score < 6,"Low Performer",
                    Score>7,"High Performer"
                )
RETURN Rating

Not sure. I made the assumption here that a score above 7 is a high performer. Otherwise, what happens to someone who falls in between 7 and 8?

 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Resolver I
Resolver I

Re: Switch & Calculate Issue

Hi @Greg_Deckler,

 

Here is an example of the data: 

 

EE IDNameFY17 RatingFY16 RatingPerformer?
1234Skellington, Jack44High
2345Pooh, Winnie43Core
3456Mouse, Mickey52Core
4567Duck, Donald13Low

 

 

I want a calculated column (added to the last column to show an example) that says:

 

  • If an employee receives >= 4 in FY16 and FY17 then they are a high performer
  • If an employee receives between a 3 and 3.5 in FY16 and FY17 then they are a core performer
  • If an employee receives less than a 3 in FY16 and FY17 then they are a low performer
  • Otherwise, "N/A" - My logic that I wrote in my original post I think is messed up somewhere.

 

The second piece to that is this: 

 

Looking at Winnie The Pooh, he has a 3 and a 4. Well, what happens to him? He gets an "N/A" based on first piece of logic because he doesn't meet that criteria. But if we take the average of those 2 years for him, he is a 3.5 which would make him a core performer. That's the logic I also want to build into this where I'm struggling. Other examples, Donald duck sums 4, but his average is 2 which makes him a low performer. 

 

I want the first piece to be as is as its own column, but then I also want another column with the second piece included too as we may need to look at it differently. Does this make sense?

 

Thank  you!!

 

Highlighted
Super User IV
Super User IV

Re: Switch & Calculate Issue

You just have to work this until you get your logic correct.

 

For example, by your stated rules, Donald Duck gets an N/A because his FY17 score is NOT less than 3.

 

Performance? = 
VAR Rating = SWITCH(
TRUE(),
[FY16]>=4 && [FY17]>=4,"High Performer", 
([FY16]>=3 && [FY16] <= 3.5) && ([FY17] >= 3 && [FY17]<=3.5),"Core Performer",
[FY16] < 3 && [FY17] < 3,"Low Performer",
"N/A"
)
RETURN Rating

For your second one, maybe something like this:

 

Performance2 = 
VAR Score = [FY17]+[FY16]/2
VAR Rating = SWITCH(
                    TRUE(),
                    Score>=4,"High Performer",                    
                    Score>=3 && Score <= 3.5,"Core Performer",
                    Score < 3,"Low Performer",
                    "N/A"
                )
RETURN Rating

These formulas work for their defined rules, but you will have to edit in your own rules.

 

 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Resolver I
Resolver I

Re: Switch & Calculate Issue

Thanks @Greg_Deckler. I didn't think to do it like this. I was clearly overcomplicating it! The "VAR" feature is really helpful. Thank you so much!

Highlighted
Resolver I
Resolver I

Re: Switch & Calculate Issue

@Greg_Deckler, quick question...

 

I have written the query the way I need to which is: 

 

Performance =
VAR Score = (RELATED('Rating-FY17'[FY17 Rating])+RELATED('Rating-FY16'[FY16 Rating]))/2
VAR Rating = SWITCH(
TRUE(),
Score = 5, "Top Performer",
Score >= 4 && Score < 5,"High Performer",
Score >= 3 && Score < 4,"Base Performer",
Score > 1 && Score < 3,"Low Performer",
"N/A"
)
RETURN Rating

 

However, I need to build in a fail safe where if FY16 or FY17 has a blank value in either then "N/A" because there isn't enough data to give a fair performance rating category. I accounted for if the value is less than 1 then "N/A" but, if for example it looks like this I would want it to say "N/A" as well:

 

Name                         Performance                   FY17 Rating             FY16 Rating                     
Jack Skellington                 N/A                               4                               0
Mary Poppins                     N/A                               0                               4  

Highlighted
Super User IV
Super User IV

Re: Switch & Calculate Issue

Perhaps:

 

Performance =
VAR Score = (RELATED('Rating-FY17'[FY17 Rating])+RELATED('Rating-FY16'[FY16 Rating]))/2
VAR Rating = SWITCH(
TRUE(),
Score = 5, "Top Performer",
Score >= 4 && Score < 5,"High Performer",
Score >= 3 && Score < 4,"Base Performer",
Score > 1 && Score < 3,"Low Performer",
"N/A"
)
RETURN IF(ISBLANK(RELATED('Rating-FY17'[FY17 Rating])) || ISBLANK(RELATED('Rating-FY16'[FY16 Rating])),"NA",Rating)

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Resolver I
Resolver I

Re: Switch & Calculate Issue

OMG is it really that simple? I was literally about to write that. Thank you so much. 

Highlighted
Super User IV
Super User IV

Re: Switch & Calculate Issue

🙂

 

Sometimes there's no need to overcomplicate or overthink things! 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors