Forum Discussion
Post PartisanCommon measure for multiple cards
HI,
I have table and columns as below. I want to create a common measure that can be used in four different cards to change the color of the text. I want to do this as all these cards will have same values (Good, Bad & Average) & so I dont want to create four seperate measures for each card. This measure will be used in Fx section of all four cards so that the text value changes as per the value. It will first check which subject is selected & based on the value of that subject for the given student, it will decide color code given in measure.
measure = if(subjectgrade is is "Good",1, if subjectgrade is "Bad",2,3)
Student table-
| Student name | Subject 1 grade | Subject 2 grade | Subject 3 grade | Subject 4 grade |
| S1 | Good | Bad | Good | Bad |
| S2 | Bad | Good | Bad | Average |
| S3 | Average | Bad | Good | Good |
| S4 | Average | Average | Average | Average |
| S5 | Average | Good | Average | Good |
| S6 | Average | Good | Average | Good |
| S7 | Good | Good | Average | Average |
Cards
- Subject 1 grade
- Subject 2 grade
- Subject 3 grade
- Subject 4 grade
Slicers-
Student name
Step 1. Transform your data into Student Column, Subject, Grade
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjZU0lFyz89PAVJOiSmonFgdoAIjbDI6So5lqUWJ6akQNcZIAmiqwRRYkQmKInwssHJTFCmoaWgCYIVmxCo0R3MVVqtjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student name" = _t, #"Subject 1 grade" = _t, #"Subject 2 grade" = _t, #"Subject 3 grade" = _t, #"Subject 4 grade" = _t]),
Unpivot = Table.UnpivotOtherColumns(Source, {"Student name"}, "Subject", "Grade")
in
UnpivotStep 2. Create a measure to define colors
Color Grade =SWITCH(SELECTEDVALUE('Sample'[Grade]);"Good";"Green";"Bad";"Red";"Average";"Gray")Example of use:Step 3. Create card viz and apply filters
3 Replies
- bolfri
Solution Sage
Step 1. Transform your data into Student Column, Subject, Grade
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjZU0lFyz89PAVJOiSmonFgdoAIjbDI6So5lqUWJ6akQNcZIAmiqwRRYkQmKInwssHJTFCmoaWgCYIVmxCo0R3MVVqtjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student name" = _t, #"Subject 1 grade" = _t, #"Subject 2 grade" = _t, #"Subject 3 grade" = _t, #"Subject 4 grade" = _t]),
Unpivot = Table.UnpivotOtherColumns(Source, {"Student name"}, "Subject", "Grade")
in
UnpivotStep 2. Create a measure to define colors
Color Grade =SWITCH(SELECTEDVALUE('Sample'[Grade]);"Good";"Green";"Bad";"Red";"Average";"Gray")Example of use:Step 3. Create card viz and apply filters
So you want this measure to return a color code for conditional formatting on a table? This measures returned value depends on the subject AND the value for that subject?
Make a switch statement to determine what the value is then another switch to determine which subject we're in. The switch expressions for the subject will contain either another switch or an if going through the different grades
harshadrokadeThanks nick_potts for your reply.
Yes I ant the measure for conditional formatting. This measure will check in which Subject card's conditional formatting it is added & the respective card is built & based on the same, it will show
