Conditional formatting based on Field value

[moumipanja]

I have 2 tables showing project details and the stage each project.

Table Project:

Table Stage:

I have created a matrix visualization that display the total revenue for each project for each month and a filter on stage:

I want to apply conditional formatting for Revenue depending on the Stage. All projects which are in Stage 1 and 2, font color for revenue should be in Orange, all projects which are in 3,4 and 5, font color for revenue should be in Green.

To achieve this, I created a calculated column in the Stage table like below:

Color = SWITCH(TRUE(),Stage[Stage]=1, "#FF4500", 
Stage[Stage]=2, "#FF4500", Stage[Stage]=3, "#008000", 
Stage[Stage]=4, "#008000",Stage[Stage]=5, "#008000","#FF1493")

I did the required modification in the Format pane like below:

This is not working the way I want. My desired output is something like below:

• If I do not select any value from Stage filter, the font for revenue value should come as Orange for all projects in Stage 1 and 2, and as Green for all projects in Stage 3,4,5.
• If I select Stage 1 and Stage 3 at the same time, all projects in Stage 1 should appear in Orange color and in Stage 3 should appear in Green.

Thank you for your help in advance!

[TomMartens]

Hey,

I'm wondering if there is some kind of realtionship betwenn both tables.

I recommend to create a measure that looks for the stage value inside the project table and use this measure for the conditionali formatting on your revenue field.

Please consider to create a pbix with some sample data, upload the file to onedrive or dropbox and share the link.

Regards,

Tom

[moumipanja]

Hi @TomMartens 

Thank you for your response. Yes, both the tables are connected. For convenience, I am sharing a pbix. here's the link: 

https://www.dropbox.com/s/tt0ib5d6429502b/test.pbix?dl=0 

Please let me know if you require further information.

And thank you once again 🙂

[TomMartens · Accepted solution]

Hey,

based on your sample file I created this measure and assigned the measure to the table: Project

viz Aid - FontColor Revenue = 
var theProject = SELECTEDVALUE('Project'[Project],BLANK())
var theLastDate = CALCULATE(MAX('Project'[Date]),'Project'[Project] = theProject) 
var theStage = CALCULATE(FIRSTNONBLANK('Project'[Stage],0),'Project'[Date] = theLastDate, 'Project'[Project]=theProject)
var theColor = LOOKUPVALUE(Stage[Color],Stage[Stage], theStage)
return
theColor

It may look somewhat complicated, but with this measure it is possible to retrieve the stage value from the last date of a project. This value is used to pull the color from the stage table using the LOOKUPVALUE(...) function.

The measure "just" returns the color that is associated with the stage value.

Then I used "Conditional Formatting" on the font color on the revenue in the matrix visual like so:

The result looks like this

And here is a link to your sample file that contains my solution:

https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/Ec8kisooDXlMhAHxAXywSLgByBuUk...

Regards,

Tom

[moumipanja]

Thank you for the great solution! I have many complicated tables and relationships in my original data with millions of rows and multiple conditions and filters. I mapped your solution to my original data and it worked perfectly.

Thank you!