Forum Discussion
Score Card using symbols
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:
The DAX that I am using for row index 12 [2023] is "
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. 🙂
9 Replies
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
I have tried this
Icon =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.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 NumberI 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. 🙂
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)))).
I tried this as well.
Icon =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.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?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])ReturnSWITCH(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%"),"")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).
