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.
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 reference
If 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.