Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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-field-value   ...
Need Help
  • molegris's avatar
    molegris
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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:

 

Seward12533's avatar
Seward12533
Solution Sage
7 years ago
No like this.

Measure Value Color = SWITCH(TRUE();
VALUE([Measure Value]) <=1;"#000000";
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

     

    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's avatar
      Seward12533
      Solution Sage
      7 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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