Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
WNelson
Frequent Visitor

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:

 
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
WNelson_0-1706805868726.png

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.  
1 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:
sevenhills_3-1706819068254.png

 

Column2 is calculated column and its syntax:

sevenhills_4-1706819097530.png

 

Measure syntax:

sevenhills_2-1706818653374.png

 

 

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

View solution in original post

9 REPLIES 9
WNelson
Frequent Visitor

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

 

WNelson_0-1706888466755.png

 

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

)

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:

sevenhills_0-1706902990543.png

(or)

sevenhills_1-1706903025346.png

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
Frequent Visitor

@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_0-1706815194639.png

 

WNelson
Frequent Visitor

@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
Super User
Super User

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

@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
 
WNelson_0-1706816164723.png

 

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

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

 
Tested:
sevenhills_3-1706819068254.png

 

Column2 is calculated column and its syntax:

sevenhills_4-1706819097530.png

 

Measure syntax:

sevenhills_2-1706818653374.png

 

 

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

@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

Glad to hear that it helped! 🙂 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors