Concatenate 3 column values as icons

[ArtisanAmbrosia]

I am tracking Sales to Pace (target) and need to display a matrix column with past 3 month's results as 3 concatenated icons.

Example with :

Here's my base DAX I've trying different methods with:

$ to Pace Cumulative = 'DAX Measures'[Created] - 'DAX Measures'[Combined Pace Cumulative Table]
$ to Pace LCM = CALCULATE([$ to Pace Cumulative], 'Date Table'[CurrMonthOffset] = -1)
$ to Pace LCM-2 = CALCULATE([$ to Pace Cumulative], 'Date Table'[CurrMonthOffset] = -2)
$ to Pace LCM-3 = CALCULATE([$ to Pace Cumulative], 'Date Table'[CurrMonthOffset] = -3)
$ to Pace LCM Icon = SWITCH(TRUE(),
'DAX Measures'[$ to Pace LCM] > 0, UNICHAR(9650),
'DAX Measures'[$ to Pace LCM] < 0, UNICHAR(9660), BLANK())

$ to Pace LCM-2 Icon = IF('DAX Measures'[$ to Pace LCM-2] > 0, "✅", "❌")
$ to Pace LCM-3 Icon = IF('DAX Measures'[$ to Pace LCM-3] > 0, "🟢", "⭕")
Combined Icons = [$ to Pace LCM-3 Icon] & " " & [$ to Pace LCM-2 Icon] & " " & [$ to Pace LCM Icon]

 The [Combined Icons] column is [Last 3 Months] in example matrix.

Since I'm trying to represent the value of [$ to Pace Cumulative] as an icon only, I haven't been able to come up with a measure for conditional formatting that correctly designates a color for the unicode triangles or an icon for the values of concatenated measures.

Ideally, I want to display these native PBI icons for above 0 and below 0: 

However, the following DAX doesn't work:

$ to Pace LCM-2 Icon = IF('DAX Measures'[$ to Pace LCM-2] > 0, "TriangleHigh", "TriangleLow")

I also couldn't get image URLs to work in this measure.

Any idea how I can display the [Last 3 Months] column using the above native Power BI green and red triangles? 

[Anonymous]

Hi, @ArtisanAmbrosia 

If you need to use the icon native to Power BI, you'll need to set up conditional formatting for your column. You can check out this community blog below:

Tips on displaying icons based on field values in ... - Microsoft Fabric Community

Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ArtisanAmbrosia]

Thank you for the blog. I would love to be able to conditionally format but it's not working with my concatenated column. I've been messing around with different DAX calculations for conditional formatting that would display a different icon for each concatenated column's results within the same column. 

I tried using gray unicode triangles instead to apply color formatting to, but it would only change the color of the 3rd icon rather than all 3.

$ to Pace Format = SWITCH(TRUE(),

CONTAINSSTRING([Combined Icons], UNICHAR(9650)), UNICHAR(9650) = "#3F9C35",

CONTAINSSTRING([Combined Icons], UNICHAR(9660)), UNICHAR(9660) = "#E40046", BLANK())

[hackcrr]

Hi, @ArtisanAmbrosia 

To display a column with the past three months' results as concatenated icons in a Power BI matrix, you need to create a measure for each month's icon and then concatenate them into a single measure. Here's a step-by-step approach to achieve this:

You already have the base measures to calculate the pace values for the last three months. Make sure these measures are defined correctly:

$ to Pace LCM = CALCULATE([$ to Pace Cumulative], 'Date Table'[CurrMonthOffset] = -1)
$ to Pace LCM-2 = CALCULATE([$ to Pace Cumulative], 'Date Table'[CurrMonthOffset] = -2)
$ to Pace LCM-3 = CALCULATE([$ to Pace Cumulative], 'Date Table'[CurrMonthOffset] = -3)

Define the icon measures based on whether the value is positive or negative. Use Unicode characters for the arrows:

$ to Pace LCM Icon = SWITCH(TRUE(),
    [$ to Pace LCM] > 0, UNICHAR(9650),   -- Up Arrow
    [$ to Pace LCM] < 0, UNICHAR(9660),   -- Down Arrow
    BLANK()
)

$ to Pace LCM-2 Icon = SWITCH(TRUE(),
    [$ to Pace LCM-2] > 0, UNICHAR(9650),   -- Up Arrow
    [$ to Pace LCM-2] < 0, UNICHAR(9660),   -- Down Arrow
    BLANK()
)

$ to Pace LCM-3 Icon = SWITCH(TRUE(),
    [$ to Pace LCM-3] > 0, UNICHAR(9650),   -- Up Arrow
    [$ to Pace LCM-3] < 0, UNICHAR(9660),   -- Down Arrow
    BLANK()
)

Create a measure that concatenates the icon measures for the last three months:

Combined Icons = 
    [$ to Pace LCM-3 Icon] & " " & [$ to Pace LCM-2 Icon] & " " & [$ to Pace LCM Icon]

Define the color codes using a measure:

$ to Pace LCM Icon Color = IF([$ to Pace LCM] > 0, "#3F9C35", "#E40046")
$ to Pace LCM-2 Icon Color = IF([$ to Pace LCM-2] > 0, "#3F9C35", "#E40046")
$ to Pace LCM-3 Icon Color = IF([$ to Pace LCM-3] > 0, "#3F9C35", "#E40046")

Assign these colors to the Combined Icons measure using a trick. 

Final Measure:

Combined Icons =
    VAR Icon1 = IF([$ to Pace LCM-3] > 0, UNICHAR(9650), UNICHAR(9660))
    VAR Icon2 = IF([$ to Pace LCM-2] > 0, UNICHAR(9650), UNICHAR(9660))
    VAR Icon3 = IF([$ to Pace LCM] > 0, UNICHAR(9650), UNICHAR(9660))
    RETURN Icon1 & " " & Icon2 & " " & Icon3

Make sure to apply the conditional formatting rules properly to reflect the correct colors in your matrix visual.

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

[ArtisanAmbrosia]

I appreciate your reply and time. It seems you've primarily repeated the DAX I already wrote and I don't understand the need for the first Combined Icons measure if I use the 2nd Combined Icons measure in the matrix. The issue of conditionally formatting each icon within the concatenated measure is still not solved. What "trick" would assign colors to each icon conditionally? Your last statement is the part I need to solve for:
"Make sure to apply the conditional formatting rules properly to reflect the correct colors in your matrix visual."

[hackcrr]

Hi, @ArtisanAmbrosia 

Using the visual objects that come with Power BI doesn't do what you mentioned about customizing colors for each icon. The Visual Objects are limited in the functionality they provide and do not allow you to fully customize each and every element within them.
If you want to achieve such functionality you need to use a custom visual object or check the Visual Objects Marketplace to see if there is a visual object that provides such functionality.

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

[Anonymous]

Hi, @ArtisanAmbrosia 

Thank you very much for your reply. In the Power BI table visual, there's no way to set a color for a single icon within a single column. Instead, it's possible to break down your three icons into 3 columns and set the color of each column. When we use conditional formatting, it's set for each cell of a column in the table visual as a whole. As shown in the figure below:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.