Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
With the latest Desktop release we got some color coding possibilities with DAX.
I am trying to implement it to my scenario.
I am using the measure "Measure Value" which returns different KPI's value depend on a slicer selection.
If I select VALUE SHARE on a slicer the measure will return value share in percentage format.
But if I select VALUE SALES IYA the measue will return value sales index versus year ago in number format.
Measure Value = var MySelection = SELECTEDVALUE('Measures 1'[MEASURE ID]) return SWITCH( TRUE(); MySelection = 1;FORMAT([VALUE SHARE];"0.0%"); MySelection = 2;FORMAT([VALUE SHARE DYA];"0.0%"); MySelection = 3;FORMAT([VALUE SALES IYA];"0"); MySelection = 4;FORMAT([VOLUME SALES IYA];"0"); MySelection = 5;FORMAT([$/SU IYA];"0"); BLANK() )
I am using Matrix table to visualize it.
Now I am going to color fields based on values from "Measure Value".
To do so I have created another measure:
Measure Value Color = SWITCH(TRUE(); [Measure Value] <=1;"#000000"; [Measure Value] <99,5;"#e60000"; [Measure Value] <100,5;"#000000"; [Measure Value] <999999;"#2d862d"; "#000000" )
No matter which KPI I select on slicer the "Measure Value Color" makes sens for all.
Then I try to apply conditional formatting for "Measure Value" in this way:
I am not able to select Measure Value Color.
It looks like it is locked. You can see it in above screen but not able to select this 😞
Am I doing sth wrong?
The example Micrsoft demonstraed on the movie seems to work but not my.
Solved! Go to Solution.
Hi,
This happens to me all the time. And everytime it's because I forgot the change the type of the measure that returns the color codes. It defaults to "number" even when your formula returns a text. You have to force it to "text". Then it wont be disable/gray-out anymore in the field pick-list.
--mo
I want something like this in bar graph in power bi. I need to show last year data in the graph and if this is year is greater than last year then the bar color would be green and if it is less then bar would be red. Is it achievable and if it is not then is there any other way to show the data like that. Thank you
Hi,
This happens to me all the time. And everytime it's because I forgot the change the type of the measure that returns the color codes. It defaults to "number" even when your formula returns a text. You have to force it to "text". Then it wont be disable/gray-out anymore in the field pick-list.
--mo
Hey @anonymous & @Seward12533,
I was facing the same issue and was able to fix it.
how did I fix?
I changed the data type to TEXT of the measure which changes the color.
Below is the measure I used to change the color. by default the data type of this measure was decimal. so it was not working.
I guess the logic behind this is, we are returning a HEX code which is in TEXT format. so if the data type is decimal it will not work but if it is TEXT it will work.
Triangle Color =
SWITCH(
TRUE(),
[Amt] > 1000, "#00d600",
[Amt] < 1000, "#d60000"
)
Thanks Seward
If I wrap SWITCH () in Value() like this:
Measure Value = var MySelection = SELECTEDVALUE('Measures 1'[MEASURE ID]) return VALUE( SWITCH( TRUE(); MySelection = 1;FORMAT([VALUE SHARE];"0.0%"); MySelection = 2;FORMAT([VALUE SHARE DYA];"0.0%"); MySelection = 3;FORMAT([VALUE SALES IYA];"0"); MySelection = 4;FORMAT([VOLUME SALES IYA];"0"); MySelection = 5;FORMAT([$/SU IYA];"0"); BLANK() ) )
I am getting an error:
Measure Value Color = SWITCH(TRUE(); VALUE([Measure Value]) <=1;"#000000"; VALUE([Measure Value]) <99,5;"#e60000"; VALUE([Measure Value]) <100,5;"#000000"; VALUE([Measure Value]) <999999;"#2d862d" )
Thanks Seward, but it doesnt change anything. Still the same error
Can you share a PBIX? Im assuming you changed your Measure Value function back to where it was?
I finally managed this in this way:
1) Created two measures as below.
Measure Value - gives me a number format which I want
Measure Value = var MySelection = SELECTEDVALUE('Measures 1'[MEASURE ID]) return SWITCH( TRUE(); MySelection = 1;FORMAT([VALUE SHARE];"0.0%"); MySelection = 2;FORMAT([VALUE SHARE DYA];"0.0%"); MySelection = 3;FORMAT([VALUE SALES IYA];"0"); MySelection = 4;FORMAT([VOLUME SALES IYA];"0"); MySelection = 5;FORMAT([$/SU IYA];"0"); BLANK() )
Measure Value2 - This one is used for color coding
Measure Value2 = var MySelection = SELECTEDVALUE('Measures 1'[MEASURE ID]) return SWITCH( TRUE(); MySelection = 1;[VALUE SHARE]; MySelection = 2;[VALUE SHARE DYA]; MySelection = 3;[VALUE SALES IYA]; MySelection = 4;[VOLUME SALES IYA]; MySelection = 5;[$/SU IYA]; BLANK() )
2. Apply color coding rules
Note. I use Measue Value in "Values" field in my visualization. Then use conditional formatting "background colors"
formatted by Rules, based on field Measure Value2
So finally I ended with both number and color formatting
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |