topic Re: Score Card using symbols in DAX Commands and Tips

Score Card using symbols

WNelson — Thu, 01 Feb 2024 16:55:07 GMT

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.

Re: Score Card using symbols

sevenhills — Thu, 01 Feb 2024 17:26:29 GMT

Can you try like this and see if it works?

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

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

Re: Score Card using symbols

WNelson — Thu, 01 Feb 2024 19:07:31 GMT

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)))).

Re: Score Card using symbols

WNelson — Thu, 01 Feb 2024 19:20:04 GMT

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?

Re: Score Card using symbols

WNelson — Thu, 01 Feb 2024 19:36:15 GMT

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

Re: Score Card using symbols

sevenhills — Thu, 01 Feb 2024 20:27:04 GMT

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. 🙂

Re: Score Card using symbols

WNelson — Thu, 01 Feb 2024 20:27:45 GMT

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

Re: Score Card using symbols

sevenhills — Thu, 01 Feb 2024 20:36:09 GMT

Glad to hear that it helped! 🙂

Re: Score Card using symbols

WNelson — Fri, 02 Feb 2024 15:44:47 GMT

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%"),

)

Re: Score Card using symbols

sevenhills — Fri, 02 Feb 2024 19:46:10 GMT

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).