Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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 😞

 

Am I doing sth wrong?

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

1 ACCEPTED SOLUTION
molegris
Advocate III
Advocate III

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.

 

molegris_0-1636573757875.png

 

--mo

View solution in original post

9 REPLIES 9
CConsultants1
Frequent Visitor

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 Untitled.png

molegris
Advocate III
Advocate III

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.

 

molegris_0-1636573757875.png

 

--mo

anonymous_98
Resolver I
Resolver I

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"
    )

 

Seward12533
Solution Sage
Solution Sage

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.
Anonymous
Not applicable

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

No like this.

Measure Value Color = SWITCH(TRUE();
VALUE([Measure Value]) <=1;"#000000";
Anonymous
Not applicable

 

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?

Anonymous
Not applicable

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 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.