Forum Discussion
Conditional formatting based on Field value
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 theColorIt 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:
Regards,
Tom
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 :)
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:
Regards,
Tom
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!