Score Card using symbols

[WNelson]

Really needing an expert on this one.....

I am creating a score card that I have pulled off You Tube.  I have the sample BI to copy and have benn doing great until now.  I am  trying to add a symbol column .  The DAX works well until I try to add  selectitem 12 that involves a %.     Here is the DAX  below:

Icon =

VAR SelectItem = SELECTEDVALUE('KPI Score Card'[Row Index])

Return

SWITCH(TRUE(),

SelectItem IN {1,9,13,17}, "",

SelectItem = 2 && [Overall Sastifaction 2024] <= 4.5,"▲",

SelectItem = 2 && [Overall Sastifaction 2024] > 4.5,"▼",

SelectItem = 5 && [Overall Sastifaction with current position] >= 4.5,"▲",

SelectItem = 5 && [Overall Sastifaction with current position] < 4.5,"▼",

SelectItem = 7 && [CP Communication with Classic] >= 4.5,"▲",

SelectItem = 7 && [CP Communication with Classic] < 4.5,"▼",

SelectItem = 10 && [Attention to safety] >= 4.5,"▲",

SelectItem = 10 && [Attention to safety] < 4.5,"▼",

SelectItem = 12 && [Classic prioritizes overall safety] >= 0.90,"▲",

SelectItem = 12 && [Classic prioritizes overall safety] < 0.90,"▼",

UNICHAR(9724)

)

 This is my visualization

The DAX that I am using for row index 12 [2023] is "

Selectitem = 12, FORMAT([Classic prioritizes overall safety], "00%"),"  (this is a text format)

Why is the DAX not working as soon as I add selectitem 12?

The error code says:   Calculation error ....do not support comparing values of type Text with values of Type Number.  Consider using the VALUE or FORMAT functionto convert one of the values.

Please help.  

[WNelson]

@sevenhills 

One more question for you if you have the time.  In my scorecard I have a couple of empty points in a column due to to data for that year.  I would like to fill the box in with shaded in area.  How do I create that when there is no data for that line.  

The 2023 measure looks like the attacheed below.  I want to add a selectitem for line 2 that I don't have a measure for.  Is that possible.  Or do I have to create a whole new measure for this?

2023 =

VAR SelectItem = SELECTEDVALUE('KPI Score Card'[Row Index])

Return

SWITCH(TRUE(),

Selectitem = 4, FORMAT([2023Turnover Rate], "00%"),

Selectitem = 3, FORMAT([I belong to a team that values], "00%"),

Selectitem = 5, FORMAT([2023 Employee job sat], "0.0"),

Selectitem = 6, FORMAT([Client agree needs met], "0%"),

Selectitem = 7, FORMAT([CP Communication with Classic], "0.0"),

Selectitem = 10, FORMAT([Attention to safety], "0.0"),

Selectitem = 12, FORMAT([Classic prioritizes overall safety], "00%"),

Selectitem = 14, FORMAT([Classic communicates well with me], "00%"),

Selectitem = 18, FORMAT([Percentage SH Growth from last year], "0%"),

Selectitem = 22, FORMAT([2023Touchpoints], "0"),

Selectitem = 19, FORMAT([Percentage Growth 2022 to 2023], "0%"),

Selectitem = 23, FORMAT([Private Growth from last year], "0%"),

Selectitem = 20, FORMAT([Success Rate for Sep-Dec 2023], "0%"),

Selectitem = 21, FORMAT([Success  PRRate for Sep-Dec 2023],"0.0%"),

""

)

[sevenhills]

I understood your question as you want to show some background ground color for the empty values.

You can do the conditional formatting. (easy in few steps you can do that)

1. Select the visual

2. Select the measure name

3. Right click, and select the conditional formatting, background color. 

Something like that:

(or)

In my view, no need for new measure and pretty much few steps. 

For the first timers, it is overwhelming. 

Check this link 

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

(or) You can watch youtube videos on this topic too.

If this is not working, you can share the .pbix file (by removing sensitie info).

[WNelson]

@sevenhills 

I tried this as well.  

Icon =

VAR SelectItem = SELECTEDVALUE('KPI Score Card'[Row Index])

Return

SWITCH(TRUE(),

SelectItem IN {1,9,13,17}, "",

SelectItem = 2 && [Overall Sastifaction 2024] <= 4.5,"▲",

SelectItem = 2 && [Overall Sastifaction 2024] > 4.5,"▼",

SelectItem = 5 && [2024 Overall Employee Sat] >= 4.5,"▲",

SelectItem = 5 && [2024 Overall Employee Sat] >= 4.5,"▼",

SelectItem = 6 && [Client agree needs met] < 90,"▼",

SelectItem = 6 && [Client agree needs met] >= 90,"▲",

SelectItem = 5 && [Overall Sastifaction with current position] < 4.5,"▼",

SelectItem = 7 && [CP Communication with Classic] >= 4.5,"▲",

SelectItem = 7 && [CP Communication with Classic] < 4.5,"▼",

SelectItem = 10 && [Attention to safety] >= 4.5,"▲",

SelectItem = 10 && [Attention to safety] < 4.5,"▼",

SelectItem = 12 && VALUE([Classic prioritizes overall safety]) <= 0.90,"▼",

SelectItem = 22 && [2024Touchpoints] <= 152,"▼",

SelectItem = 22 && [2024Touchpoints] > 152,"▲",

UNICHAR(9724)

)

But still get the error Cannot convert the value 98% of type text to type number.

It is refering to this?

[WNelson]

@sevenhills 

Nope this is the error that I get.  

The syntax for 'SelectItem' is incorrect. (DAX(VAR SelectItem = SELECTEDVALUE('KPI Score Card'[Row Index])ReturnSWITCH(TRUE(),SelectItem IN {1,9,13,17}, "",SelectItem = 2 && [Overall Sastifaction 2024] <= 4.5,"▲",SelectItem = 2 && [Overall Sastifaction 2024] > 4.5,"▼",SelectItem = 5 && [2024 Overall Employee Sat] >= 4.5,"▲",SelectItem = 5 && [2024 Overall Employee Sat] >= 4.5,"▼",SelectItem = 6 && [Client agree needs met] < 90,"▼",SelectItem = 6 && [Client agree needs met] >= 90,"▲",SelectItem = 5 && [Overall Sastifaction with current position] < 4.5,"▼",SelectItem = 7 && [CP Communication with Classic] >= 4.5,"▲",SelectItem = 7 && [CP Communication with Classic] < 4.5,"▼",SelectItem = 10 && [Attention to safety] >= 4.5,"▲",SelectItem = 10 && [Attention to safety] < 4.5,"▼",SelectItem = 12 && Value([Classic prioritizes overall safety]) >= 0.90SelectItem = 22 && [2024Touchpoints] <= 152,"▼",SelectItem = 22 && [2024Touchpoints] > 152,"▲",UNICHAR(9724)))).

[sevenhills]

Can you try like this and see if it works?

SelectItem = 12 && [Classic prioritizes overall safety] >= 0.90

as

SelectItem = 12 && Value([Classic prioritizes overall safety]) >= 0.90

[WNelson]

@sevenhills 

I have tried this

Icon =

VAR SelectItem = SELECTEDVALUE('KPI Score Card'[Row Index])

Return

SWITCH(TRUE(),

SelectItem IN {1,9,13,17}, "",

SelectItem = 2 && [Overall Sastifaction 2024] <= 4.5,"▲",

SelectItem = 2 && [Overall Sastifaction 2024] > 4.5,"▼",

SelectItem = 5 && [2024 Overall Employee Sat] >= 4.5,"▲",

SelectItem = 5 && [2024 Overall Employee Sat] >= 4.5,"▼",

SelectItem = 6 && [Client agree needs met] < 90,"▼",

SelectItem = 6 && [Client agree needs met] >= 90,"▲",

SelectItem = 5 && [Overall Sastifaction with current position] < 4.5,"▼",

SelectItem = 7 && [CP Communication with Classic] >= 4.5,"▲",

SelectItem = 7 && [CP Communication with Classic] < 4.5,"▼",

SelectItem = 10 && [Attention to safety] >= 4.5,"▲",

SelectItem = 10 && [Attention to safety] < 4.5,"▼",

SelectItem = 12 && Value([Classic prioritizes overall safety]) >= 0.90,"▼",

SelectItem = 22 && [2024Touchpoints] <= 152,"▼",

SelectItem = 22 && [2024Touchpoints] > 152,"▲",

SelectItem = 19 && [Total Employees 2024] <= 39,"▼",

SelectItem = 19 && [Total Employees 2024] <= 39,"▲",

SelectItem = 19 && [2024Touchpoints] > 152,"▲",

" "

)

The error I get is : cannot convert value 98% type Text to type Number

[sevenhills · Accepted solution]

I think you have % part of the text/value. Try this.

SelectItem = 12 && (VALUE( SUBSTITUTE([Classic prioritizes overall safety],"%","") ) / 100) >= 0.90

Tested:

Column2 is calculated column and its syntax:

Measure syntax:

I was doing fast copy paste and has to do multiple edits in this reply. 🙂

[WNelson]

@sevenhills 

You are my HERO!!!!!!!  thank you so so much.  This has been making me crazy.  I appreciate you willingness to help.  

Thank you,

Wendy

[sevenhills]

Glad to hear that it helped! 🙂