Help with Dax for Custom Formatting

DAX DAX Calculations
1

let me share a scenario here:

s1
s1733×177 23.2 KB

s2
s3

When I select any observation on a table on left, on the right side, it displays the comment that was submitted by the client (and its being pulled from a different table). I have used the following DAX for that.

Selected Survey Comment: = IF(HASONEVALUE(‘Sentiment Fact’[Clean Comments]),VALUES(‘Sentiment Fact’[Clean Comments]), “Client Did not Submit Any Feedback”)

It works fine until here.

To these comments I added icons using DAX under custom formatting. Here is the DAX I wrote.

SentementIcons =
VAR SScore= VALUES(‘Sentiment Fact’[Sentement Score])
Return
SWITCH(TRUE(),
SScore < 0.45, “SymbolLow”,
SScore < 0.6, “SymbolMedium”,
SScore <= 1, “SymbolHigh”,
SScore == BLANK(), " "
)

The icons are being assigned based on sentiment score column in the table from where comments are being fetched.

It all works fine as long as I have a selection. the moment I unselect an observation, I get the following error

error1

I attempted to fixing the DAX with following changes , but that’s incorrect as well.
SentementIcons1 =
VAR SScore= VALUES(‘Sentiment Fact’[Sentement Score])
VAR Result =
SWITCH(TRUE(),
SScore < 0.45, “SymbolLow”,
SScore < 0.6, “SymbolMedium”,
SScore <= 1, “SymbolHigh”,
SScore == BLANK(), " "
)
Return
IF(HASONEVALUE(‘Sentiment Fact’[Sentement Score]),Result," ")

I am getting what I need when I have a selection on the table in left, that is to show a comment on the right.
The problem is when there is no selection. Any suggestions on how I Can fix this please -

If there is no selection, there should be no icon.

2

Hello @jps,

Thank You for posting your query onto the Forum.

Firstly, it’s always a little bit difficult for the forum members to provide a solution in a better and efficient manner without looking at the data or working PBIX file for the reference since it just involves quite a lot of guessing work and consumes most of their time.

Now, coming back to your scenario, it’s looks like “VALUES()” function used in the measure - “SentementIcons” is causing the problem here. It’s because when nothing is selected “VALUES()” function will render the entire list of a column in the form of a table since it’s a table function.

So, if your “Sentement Score” column under the “Sentiment Fact” table is a numerical field then rather than using table function use the “Aggregating” or “Iterating” function which converts your final results into the “Scalar Values” rather than “List or Table Values”.

So for testing purposes can you please try the revised measure provided below to check the results?

SentementIcons =
VAR SScore = 
SUMX( 
    'Sentiment Fact' , 
    'Sentiment Fact'[Sentement Score] )

RETURN
SWITCH(TRUE(),
    SScore = BLANK() , " "
    SScore < 0.45, "SymbolLow" ,
    SScore < 0.6, "SymbolMedium" ,
    SScore <= 1, "SymbolHigh"
)

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Important Note: If this still doesn’t solves your query then please provide the working or mock-up PBIX file for the reference.

Thanks and Warm Regards,
Harsh

1 Like
3

I get your point. - from now on ill try to share a PBX file where ever I can.

Your suggested change worked just fine - it gives me a blank Square box - like a placeholder for an icon.
noerror
It Serves My Purpose for now. I Really Appreciate your support.

Thank You

1 Like
4

Hello @jps,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh

1 Like