cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gordon2 Member
Member

Color coding with DAX

With the latest Desktop release we got some color coding possibilities with DAX.

 

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-formatting-by-f...

 

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:

 

 

Capture.PNG

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 Smiley Sad

 

Am I doing sth wrong?

The example Micrsoft demonstraed on the movie seems to work but not my.

6 REPLIES 6
Seward12533 New Contributor
New Contributor

Re: Color coding with DAX

Your Measure Calue is TEXT Has to be numeric. Try wrapping the Measure Value within your SWITCH of your color measure with a VALUE() to concert to a number.
Gordon2 Member
Member

Re: Color coding with DAX

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:

 

Capture1.PNG

Seward12533 New Contributor
New Contributor

Re: Color coding with DAX

No like this.

Measure Value Color = SWITCH(TRUE();
VALUE([Measure Value]) <=1;"#000000";
Gordon2 Member
Member

Re: Color coding with DAX

 

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

 

Seward12533 New Contributor
New Contributor

Re: Color coding with DAX

Can you share a PBIX?  Im assuming you changed your Measure Value function back to where it was?

Highlighted
Gordon2 Member
Member

Re: Color coding with DAX

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

Capture5.PNG

 

So finally I ended with both number and color formatting