Forum Discussion

moumipanja's avatar
moumipanja
Microsoft Employee
7 years ago
Solved

Conditional formatting based on Field value

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 p...
Need Help
Show and Tell
  • TomMartens's avatar
    TomMartens
    7 years ago

    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/Ec8kisooDXlMhAHxAXywSLgByBuUkxQvYRFm0RNflcze1A?e=SP2YwZ

     

    Regards,

    Tom

     

TomMartens's avatar
TomMartens
Super User
7 years ago

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

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    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/Ec8kisooDXlMhAHxAXywSLgByBuUkxQvYRFm0RNflcze1A?e=SP2YwZ

     

    Regards,

    Tom

     

    • moumipanja's avatar
      moumipanja
      Microsoft Employee
      7 years ago

      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!