SWITCH Statement for Quick Measure

[lcharlebois]

Hi all,

I'm trying to create a new measure that will return 1 or 0 based on the value of a column, so that I can then use that measure to do some conditional formatting on tables fields.  Here's what I have for the new measure currently:

Letter Grade Number =

VAR LetterGrade = SELECTEDVALUE('db student_record'[letter_grade])

RETURN

SWITCH(TRUE(),

LetterGrade = "EX", 1,

LetterGrade = "P", 1,

LetterGrade = "A", 1,

LetterGrade = "B", 1,

LetterGrade = "C", 1

)

And in the Conditional Formatting for Background color, I have this.

Idea is, the column should show a red background if the db student_record'[letter_grade] field is anything but the values listed in the SWITCH statement.  What seems to be happening though is only a value of "P" is showing the white background - all others listed in the SWITCH statement - and any other value - is showing in red. 

[lcharlebois]

Hi again @tamerj1 - revisiting this as it's still not working.  Wanted to provide some context with some screenshots to hopefully help.

1 - Cole is highlighted in red in the table on the left.  But the table on the right, all rows are not in red.

2 - If I change his Working at Heights grade from an "EX" to a "P", the report works as intended.  i.e. His row in the table on the left is not highlighted in red (it's slightly grey only because it's currently selected).

3 - This is the conditional formatting on the columns for the table on the left  (only showing the Mobile column here).

4 - This is the conditional formatting on the columns for the table on the right (only showing Course Name column here) .

5 - This is the custom column "Letter Grade Number" that I'm using in the conditional formatting for the fields in both tables

So the thing is, that custom column and the conditional formatting is working for the table on the right.  Because above, Cole's Working at Heights record with an "EX" isn't showing up as red.  But the custom column and conditional formatting isn't working for the table on the left.

Any help, again, much appreciated.

[Anonymous]

Hi @lcharlebois ,

I have checked the formula, it works well.

Please refer to my pbix file.

If it is possible, please provide your pbix file without privacy information for us to study.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @lcharlebois 

you need to check your source data for visible and invisible spaces and characters. You need to use power quert to trim and clean the data. This could be the issue or one of many. 

[lcharlebois]

Hi @tamerj1  -  thanks for the quick reply.

I definitely considered that, and updated the values on some test records manually rather than through our Java app, with the same result.

[tamerj1]

@lcharlebois 

I think SELECTEDVALUE returns blank for most of the rows as a result of finding many values. Please Try measure:

SELECTEDVALUE('db student_record'[letter_grade])

and see what results you receive.