Forum Discussion
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 RatingThese formulas work for their defined rules, but you will have to edit in your own rules.
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 RatingThese formulas work for their defined rules, but you will have to edit in your own rules.
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!
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 RatingHowever, 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 4Perhaps:
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)OMG is it really that simple? I was literally about to write that. Thank you so much.