Complex Conditonal Formatting

[chriswoodiow]

I am trying to have 2 different icons next to the furthest left column, however if there is no data it will not display the icon. is there something I'm doing wrong?

[v-pagayam-msft]

Hi @chriswoodiow ,
I wanted to follow up on our previous suggestions regarding Complex conditional formatting

We would like to hear back from you to ensure we can assist you further. If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

[v-pagayam-msft]

Hi @chriswoodiow ,
I hope the suggested solution worked for you. If your issue is resolved, kindly accept the post as a solution — it helps the community identify helpful answers more easily.If you found any other answer, please share it here and accept it as solution.

Thank you.

[chriswoodiow]

The below might help with the suggestions on what I am trying to achieve. 
I am currently using the conditonal formatting on the Building/Ship based on the name varies the icon used. 

However if there is no data for the month the icon will not display. 

I'm not sure the above solution will work with that. 

[v-pagayam-msft]

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.

Thank you.

[chriswoodiow]

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.

[v-pagayam-msft]

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.

[v-pagayam-msft]

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.

[Akash_Varuna]

Hi @chriswoodiow You may need to address the blank values and make the measure something like:
IconSelector = IF(ISBLANK([Column]), BLANK(), IF([Column] > Threshold, "Icon1", "Icon2")).
Apply this measure in your visual, using conditional formatting set to Field Value to display the appropriate icons.
Ensure that blanks are excluded either in the measure logic or by applying a visual-level filter.

[maruthisp]

Hi chriswoodiow,

You may need to handle BLANK values by creating a measure or caluclated column that returns a value or flag for icon logic. Example DAX as below:

IconFlag =

IF(

    ISBLANK([YourColumn]),

    -1,  // Use -1 to represent blank

    [YourColumn]

)

Then apply icon formatting properly to handle blanks values.

OR

If you want two different icons , you can use a calculated column that returns a Unicode character or image URL based on logic, and display that in a separate column.

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[danextian]

Hi @chriswoodiow 

I would follow @Irwan 's suggestion if adding an extra column your table visual is not an issue. Alternatively, you can actually use a DAX-generated SVG measure as a conditional formatting icon https://youtu.be/SsmvAGSk8Ck This approach will give you more liberty as to the look and feel of the icon/s

[Irwan]

hello @chriswoodiow 

perhaps using measure if statement with UNICHAR() or UNICODE()

Hope this will help.

Thank you.

[vicky_]

At the moment, you can only get one icon per cell (using the default settings). If you want to have two icons, then you will need to create your own icon(s) - like the following: https://jihwankimcscp.wixsite.com/supplychainflow/post/enhancing-power-bi-custom-themes-with-new-ico...

Then you can use a switch statement to map the icon to your conditions:

Conditional Format Measure = SWITCH(TRUE(), 
   condition1, 1, 
   condition2, 2,
   ... etc.
)

Once you apply the icons, you can change the Rules section to 

If value = 1 Number then (your custom icon)

...

If value is blank then No Icon