Conditional Formatting with IF statement

[Anonymous]

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!

[Anonymous]

@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 wrong

[kpost]

whoops... 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//

[Anonymous]

This worked thank you so much I appreciate it! does the SWITCH essentially make that part of the formula dynamic?

[kpost]

You're very welcome, I'm glad the solution worked for you.

A SWITCH/TRUE()  Statement is useful for any time you want to write a measure that resolves to a single value, but there are many different situations you want to take into account.  Perfect for a situation such as this one.

Switch/true statements work by going line by line, evaluating the first argument on each line, and if it's true then it returns the second value.  It continues until it finds a true statement, then it stops.

To put it in a context I know you already understand due to your use of an IF statement in your original question, a SWTICH/TRUE() statement is basically an easier to write and easier to read version of nested if/else statements.

The answer I gave is equivalent to this one:

Conditional_Format_color = 
var engagement = [engagement rate]
var media_company = SELECTEDVALUE(TABLE[Social_Media_Company])
RETURN

IF( media_company = "Facebook" && engagement < 0.005,
      1,
      IF(media_company = "Facebook",
           0,
             IF(etc...)))


//Mediocre Power BI Advice, but it's free//

[Anonymous]

aahhh ok that makes sense thank you for explaining!

[kpost · Accepted solution]

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//