Forum Discussion

Ben_Cates's avatar
Ben_Cates
Frequent Visitor
1 year ago
Solved

Conditional Format column chart based on separate date table

Hi everyone,   I have a basic column chart with a date table on the x-axis, and values on the y.  The end user can select to view different charts using the "Ramp" slicer.  For each of the values i...
Need Help
  • elitesmitpatel's avatar
    elitesmitpatel
    1 year ago

    Hi Ben_Cates 
    Below is the solution 

    Steps :
    1) create a measure 

     
    SelectedColor =
    SWITCH(
        TRUE(),
        SELECTEDVALUE(Facts[Ramp]) = "Calgary" && LOOKUPVALUE(TransitionDate[Transition Date],  TransitionDate[Ramp], "Calgary") >MAX(Facts[Date]),"#ec8fca",  
        SELECTEDVALUE(Facts[Ramp]) = "Edmonton" && LOOKUPVALUE(TransitionDate[Transition Date],  TransitionDate[Ramp], "Edmonton") >MAX(Facts[Date]),"#FF0000"
    )
     
    2)  select the column chart and go to Format --> Columns --> Color --> fx

    3) after clicking on fx in fromat style select field value  and below that  select the previously created measure.

     

    add more conditions and colour accoding to your choice in measure.
    Below is the file link :- 

    Dummy data solved 

    If you get some value Please Kudo's our effort  and Accept it as solution so other can be benefited. 

elitesmitpatel's avatar
elitesmitpatel
Solution Supplier
1 year ago

please share dummy data or file

  • Ben_Cates's avatar
    Ben_Cates
    Frequent Visitor
    1 year ago

    Here's a link to the dummy .pbix. And a better screenshot below of what I'm trying to achieve.

     

    Both ramps have monthly values from January 2023 through September 2024.  Calgary has a separate transition date of May 1, 2024, so every column prior to that on the chart needs to be a different colour.  When a user slices the chart to display Edmonton, which has a transition date of July 1, 2023, the chart needs to update the formatting so that every column prior to July 1 has a different colour.

     

    https://drive.google.com/file/d/1One0TErghoLo79Mw6CE0BV90PG8BPg4H/view?usp=sharing 

     

     

    • elitesmitpatel's avatar
      elitesmitpatel
      Solution Supplier
      1 year ago

      Hi Ben_Cates 
      Below is the solution 

      Steps :
      1) create a measure 

       
      SelectedColor =
      SWITCH(
          TRUE(),
          SELECTEDVALUE(Facts[Ramp]) = "Calgary" && LOOKUPVALUE(TransitionDate[Transition Date],  TransitionDate[Ramp], "Calgary") >MAX(Facts[Date]),"#ec8fca",  
          SELECTEDVALUE(Facts[Ramp]) = "Edmonton" && LOOKUPVALUE(TransitionDate[Transition Date],  TransitionDate[Ramp], "Edmonton") >MAX(Facts[Date]),"#FF0000"
      )
       
      2)  select the column chart and go to Format --> Columns --> Color --> fx

      3) after clicking on fx in fromat style select field value  and below that  select the previously created measure.

       

      add more conditions and colour accoding to your choice in measure.
      Below is the file link :- 

      Dummy data solved 

      If you get some value Please Kudo's our effort  and Accept it as solution so other can be benefited.