Field Parameters- Conditional Formatting

Problem Description: While using Field Parameters, we want to do conditional formatting using the selected Axis of the selected measure value.

Model: I am using the standard sales model, which I am using for all my videos and blogs. Sales fact with a key measure [net], joined with dimensions: Item, Geography, Date, and Customer.

 

Solution: We need to get the selected value of the Axis or Measure field parameters and use that in field conditional formatting using a color measure. 

Created Axis Field Parameters

Axis Slicer = {
    ("Brand", NAMEOF('Item'[Brand]), 0),
    ("Category", NAMEOF('Item'[Category]), 1),
    ("Sub Category", NAMEOF('Item'[Sub Category]), 2),
    ("State", NAMEOF('Geography'[State]), 3),
    ("City", NAMEOF('Geography'[City]), 4)
}

Created Measure Field Parameters

Measure Slicer = {
    ("Net", NAMEOF('Measure'[Net]), 0),
    ("Gross", NAMEOF('Measure'[Gross]), 1),
    ("COGS", NAMEOF('Measure'[COGS]), 2),
    ("Discount", NAMEOF('KPI'[Discount]), 3),
    ("Discount %", NAMEOF('Measure'[Discount %]), 4),
    ("Margin %", NAMEOF('Measure'[Margin %]), 5)
}

Created a dynamic visual using that

 

Create a selected value for the axis and measure the slicers 

Selected Axis = maxx(FILTER('Axis Slicer', 'Axis Slicer'[Axis Slicer Order] = SELECTEDVALUE('Axis Slicer'[Axis Slicer Order])), 'Axis Slicer'[Axis Slicer])

Selected Measure = maxx(FILTER('Measure Slicer', 'Measure Slicer'[Measure Slicer Order] = SELECTEDVALUE('Measure Slicer'[Measure Slicer Order] )), 'Measure Slicer'[Measure Slicer])

These are only for the single value selection. 

 

The color measures are

Axis Color = Switch([selected Axis],
    "Brand", "Red",
    "Category", "Blue",
    "Sub Category", "Green",
    "State", "Yellow",
    "City", "Grey"
)

Measure Color = Switch([Selected Measure],
    "Net", "Red",
    "Gross", "Blue",
    "COGS", "Green",
    "Discount", "Yellow",
    "Discount %", "Grey",
 "Margin %", "Purple"
)

Then this is how it looks like, Axis Color (Page1)

 

Measure Color, on another visual (Page2)

 

You can combine both the selected measure and selected axis code and have more complex conditional formatting. Try that out

You can also force the measure’s value-based conditional formatting on selected measure example

Measure Color = Switch([Selected Measure],
    "Net", "Red",
    "Gross", "Blue",
    "COGS", "Green",
    "Discount", "Yellow",
    "Discount %", "Grey",
 "Margin %", Switch(True(), [Margin %] <.125, "Pink", "Purple")
)

 

 

File is attached below.

 

Find all my Medium blogs here

Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.

You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share

Master Power BI

Expertise Power BI

Power BI For Tableau User

DAX for SQL Users