Reply
Ben_Cates
Frequent Visitor
Partially syndicated - Outbound

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 in the Ramp field, there is a separate table consisting of a single specific Transition Date per ramp.  I'd like to colour the columns on the main chart so that if the dates are before the Transition Date, they have a different colour, resulting in each Ramp having it's own colour pattern.

 

Any ideas?  Appreciate any insight!

 

Ben_Cates_0-1726229201626.png

Ben_Cates_1-1726229487610.png

 

 

1 ACCEPTED SOLUTION

Syndicated - Outbound

Hi @Ben_Cates 
Below is the solution 

elitesmitpatel_0-1726257944814.pngelitesmitpatel_1-1726257970617.png

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
elitesmitpatel_3-1726258211018.png

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

elitesmitpatel_5-1726258366140.png

 

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. 

View solution in original post

4 REPLIES 4
Ben_Cates
Frequent Visitor

Syndicated - Outbound

That worked!  Thank you so much for your expertise, @elitesmitpatel 

elitesmitpatel
Solution Supplier
Solution Supplier

Syndicated - Outbound

please share dummy data or file

Syndicated - Outbound

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 

 

Ben_Cates_0-1726245756364.png

 

Syndicated - Outbound

Hi @Ben_Cates 
Below is the solution 

elitesmitpatel_0-1726257944814.pngelitesmitpatel_1-1726257970617.png

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
elitesmitpatel_3-1726258211018.png

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

elitesmitpatel_5-1726258366140.png

 

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. 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)