Using SWITCH for Dynamic Color Formatting in Power BI

In Power BI, setting colors manually for each visual can be tedious and unscalable, especially when you deal with multiple visuals or reports. A more efficient approach is to use DAX to dynamically assign colors based on conditions using the SWITCH function. Below are a few practical examples of how to implement this.

1. Example: Conditional Colors for a Percentage Comparison

Measure: CF Color vs PM

CF Color vs PM = 
VAR score = [CF vs PM %] 
RETURN 
SWITCH( 
	TRUE(), 
	score >= 0.05, "green", -- Green for scores above or equal to 5%
 	score > 0, "#E7C163", -- Gold for positive scores less than 5% 
	score = 0, "white", -- White for neutral scores (0%) 
	"red" -- Red for negative scores 
)

Explanation:

• This measure dynamically assigns:

  • Green for scores ≥ 5%.

  • Gold for positive scores less than 5%.

  • White for neutral scores.

  • Red for negative scores.

2. Example: Simple Positive-Negative Color Coding

Measure: Color Funded Balance vs PM %

Color Funded Balance vs PM % = 
VAR score = [Funded Balance vs PM %]
RETURN
SWITCH(
    TRUE(),
    score >= 0, "green",   -- Green for non-negative scores
    "red"                  -- Red for negative scores
)

Explanation:

• This measure uses a straightforward check:

  • Green for zero or positive scores.

  • Red for negative scores.

3. Example: Color Coding for Profit & Loss (P&L)

Measure: P&L ColumnColor

P&L ColumnColor = 
SWITCH(
    TRUE(),
    SUM('ADF - P&L'[Value]) > 0, "#44546A",  -- Dark blue for positive P&L
    SUM('ADF - P&L'[Value]) < 0, "#E7C163"   -- Gold for negative P&L
)

Explanation:

• This measure assigns:

  • Dark blue for positive P&L values.

  • Gold for negative P&L values.

4. Example: Using HEX Codes and Names Together

Measure: Sales Performance Color

Sales Performance Color = 
VAR performance = [Sales Growth %]
RETURN
SWITCH(
    TRUE(),
    performance >= 0.1, "#28A745", -- Green (HEX) for growth ≥ 10%
    performance >= 0.05, "lightgreen", -- Light green for growth ≥ 5% and < 10%
    performance > 0, "yellow",     -- Yellow for positive growth less than 5%
    performance = 0, "grey",       -- Grey for no growth
    "red"                          -- Red for negative growth
)

Explanation:

• Mixes HEX and named colors for better readability.

• Useful for applying corporate colors or custom themes.

Key Advantages of Using DAX for Colors:

1. Consistency: Ensures consistent color schemes across all visuals.

2. Scalability: Easily apply to multiple reports or pages.

3. Efficiency: No need to manually set color rules in each visual.

By using SWITCH with DAX for color coding, you maintain professional, consistent, and scalable reports in Power BI without the hassle of manual configuration. 👍