Forum Discussion
Conditional Formatting with IF statement
Hey All
Like the title says I am trying to do conditional formatting with an if statement but based on social media platforms' engagement rate and each individual platform has its own KPI. Example below
IF [Engagement Rate] for [Facebook] is <0.50% Then green else Red
Engagement Rate is the Measure, Facebook is a row in a column and I am trying to get it in a table format
Then repeat the above for other platforms. Appreciate any help in advance!
I'll do the best I can with the info provided...
BIG PICTURE, this is what you want to do, ASSUMING the social media company is going to be in the visual, and ASSUMING it's going to be something like a table visual....
Create a measure that returns a value like 0,1,2,3 etc depending on the conditions and the colors you want. Maybe 0 will be red, 1 will be green, 2 will be orange, 3 will be blue. Idk, whatever you want.
Note that this works because the switch statement stops evaluating once it reaches a statement that resolves to TRUE().
Conditional_Format_color =
var engagement = [engagement rate]
var media_company = SELECTEDVALUE(TABLE[Social_Media_Company])
RETURN
SWITCH(
TRUE(),
media_company = "Facebook" && engagement < 0.005, 1,
media_company = "Facebook", 0
----continue adding companies and return values here
)
Now, once you have done that, format the [Engagement Rate] field in your visual using the field value [Conditional_Format_color]. Just map each number to the corresponding color using the "Rules" you want, and voila.
//Mediocre Power BI Advice, but it's free//
6 Replies
I'll do the best I can with the info provided...
BIG PICTURE, this is what you want to do, ASSUMING the social media company is going to be in the visual, and ASSUMING it's going to be something like a table visual....
Create a measure that returns a value like 0,1,2,3 etc depending on the conditions and the colors you want. Maybe 0 will be red, 1 will be green, 2 will be orange, 3 will be blue. Idk, whatever you want.
Note that this works because the switch statement stops evaluating once it reaches a statement that resolves to TRUE().
Conditional_Format_color =
var engagement = [engagement rate]
var media_company = SELECTEDVALUE(TABLE[Social_Media_Company])
RETURN
SWITCH(
TRUE(),
media_company = "Facebook" && engagement < 0.005, 1,
media_company = "Facebook", 0
----continue adding companies and return values here
)
Now, once you have done that, format the [Engagement Rate] field in your visual using the field value [Conditional_Format_color]. Just map each number to the corresponding color using the "Rules" you want, and voila.
//Mediocre Power BI Advice, but it's free//kpost Thanks for your response! I added the above information that you suggest and you were right it was a table....I am trying to replicated your formula above and I run into issues with the switch statement here is what I have
SWITCH(TRUE(),media_company = "Facebook" && engagement < 0.005, 1,media_company = "Facebook" && engagement > 0.005,0)I am guessing it's gotta be a parenthesis thing but I could be wrongwhoops... silly mistake on my part. add a RETURN statement.
Here's the measure with the RETURN added in the right place, after the variable declarations.
Conditional_Format_color =
var engagement = [engagement rate]
var media_company = SELECTEDVALUE(TABLE[Social_Media_Company])
RETURN
SWITCH(
TRUE(),
media_company = "Facebook" && engagement < 0.005, 1,
media_company = "Facebook", 0
----continue adding companies and return values here
)
Also, you don't have to specify the "&& engagement > 0.005" part like you did here:
"media_company = "Facebook" && engagement > 0.005"
that line will ONLY evaluate if the previous line was false. So logically, all you have to do is check the name of the social media company and make sure it's facebook, then you can be sure the return value is 0
The lines evaluate in the order they are listed.
This evaluates first:
media_company = "Facebook" && engagement < 0.005, 1,
Then this evaluates:
media_company = "Facebook", 0
Since we already know the engagement is not below 0.005, we just want to return 0, for red. Because you said in your original post, "IF [Engagement Rate] for [Facebook] is <0.50% Then green else Red"
//Mediocre Power BI Advice, but it's free//