Forum Discussion
Complex Conditonal Formatting
Hi chriswoodiow ,
Thanks again for the followup!
I tried to recreate on my local.Please follow the below stesp:
1.Created a separate calender table and created relationship between Calender table and Fact table.
2.Create a new measure for icon selector using below:Icon Selector =
VAR CurrentMonthData = CALCULATE(
MAX('FactTable'[BuildingShipColumn]),
ALLEXCEPT('Calendar', 'Calendar'[Month])
)
RETURN
SWITCH(
TRUE(),
ISBLANK(CurrentMonthData), -1, -- No data case
CurrentMonthData = "Building", 1,
CurrentMonthData = "Ship", 2,
BLANK()
)3.Add a dummy measure as it ensures the visual renders rows even when there’s no data in FactTable.
Then add a table visual from Calender[Month] and dummy measure to the table.
4.Apply conditional formatting.To apply conditional formatting with icons in Power BI, select the table visual, click the Dummy column under Values, then go to Visualizations > Format Visual > Cell elements. Choose Dummy, toggle on Icons, set Format style to Rules, and pick the Icon Selector measure. Add rules: -1 for "No icon", 1 for "Building" (house), 2 for "Ship" (boat). Set Apply to as Values only, then close the pane.Select the icons as per your requirement.Ensure "Show Items with No Data" Is Enabled for Calender[Month]column.
5.Now select the slicer visual and add Calender[YearMonth].
Please refer the screenshot and file for your referenceIf this asnwer meets your requirement,give us kudos and consider accepting it as solution.If still facing in resolving ,feel free to reachout!
Thank you.
Regards,
Pallavi G.
Hi chriswoodiow ,
Thank you for the followup!
Ensure You Have a Complete Calendar Table.
You must have a proper calendar table in your model that covers all relevant months. Is related ideally one-to-many to your fact table and is used in the visual as the axis e.g., Calendar[Month], then create a measure that handles blanks.
Icon Selector =
SWITCH( TRUE(),
Isblank(MAX('FactTable'[Buildingshipcolumn])), -1,
MAX('FactTable'[Buildingshipcolumn]) = "Building", 1,
MAX('FactTable'[Buildingshipcolumn]) = "Ship", 2,
Blank())
While you build your visual,
add Calendar[Month] as row, add the appropriate field (e.g., BuildingShipColumn) or dummy column to anchor icon formatting. Apply conditional formatting using:
Format by > Field value
Based on field > Icon Selector measure
Add rules for 1, 2, and optionally -1
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Hello,
That is giving me the same result. When I select the month using the slicer it removes the icons if there is any zero results.
It will not allow the field value to be selected in conditional formatting.
Hi chriswoodiow ,
Thanks again for the followup!
I tried to recreate on my local.Please follow the below stesp:
1.Created a separate calender table and created relationship between Calender table and Fact table.
2.Create a new measure for icon selector using below:Icon Selector =
VAR CurrentMonthData = CALCULATE(
MAX('FactTable'[BuildingShipColumn]),
ALLEXCEPT('Calendar', 'Calendar'[Month])
)
RETURN
SWITCH(
TRUE(),
ISBLANK(CurrentMonthData), -1, -- No data case
CurrentMonthData = "Building", 1,
CurrentMonthData = "Ship", 2,
BLANK()
)3.Add a dummy measure as it ensures the visual renders rows even when there’s no data in FactTable.
Then add a table visual from Calender[Month] and dummy measure to the table.
4.Apply conditional formatting.To apply conditional formatting with icons in Power BI, select the table visual, click the Dummy column under Values, then go to Visualizations > Format Visual > Cell elements. Choose Dummy, toggle on Icons, set Format style to Rules, and pick the Icon Selector measure. Add rules: -1 for "No icon", 1 for "Building" (house), 2 for "Ship" (boat). Set Apply to as Values only, then close the pane.Select the icons as per your requirement.Ensure "Show Items with No Data" Is Enabled for Calender[Month]column.
5.Now select the slicer visual and add Calender[YearMonth].
Please refer the screenshot and file for your referenceIf this asnwer meets your requirement,give us kudos and consider accepting it as solution.If still facing in resolving ,feel free to reachout!
Thank you.
Regards,
Pallavi G.Hi chriswoodiow ,
Just checking in — did the solution provided help resolve your issue? If yes, please consider marking it as the accepted answer so it can help others facing a similar challenge.If still needed further assistance, feel free to reachout!
Thank you.