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
