cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Using DAX for Conditional Formatting

I am trying to create the table below in PBi (without the Ref Target column). image.pngI am having 2 issues, but they are linked:
1. Trying to control conditional formatting using DAX. I am currently using the DAX formula below:
     

Backlog Color Formatting =
VAR _30days = (CALCULATE(
            CALCULATE(COUNTA('workorder'[Backlog Timeframe]),
        'workorder'[Backlog Timeframe] = "Within 30 Days") / CALCULATE(COUNTA(workorder[WorkOrder Number]))))

VAR _60days = (CALCULATE(   
        CALCULATE(COUNTA('workorder'[Backlog Timeframe]),
        'workorder'[Backlog Timeframe] = "31 - 60 Days") / CALCULATE(COUNTA(workorder[WorkOrder Number]))))

VAR __61days = (CALCULATE(  
        CALCULATE(COUNTA('workorder'[Backlog Timeframe]),
        'workorder'[Backlog Timeframe] = "61+ Days") / CALCULATE(COUNTA(workorder[WorkOrder Number]))))
RETURN
SWITCH(TRUE(),
_30days <=.84, "#C6EFCE",
_30days >= .85,"#FFC7CE",
_60days <=.09, "#C6EFCE",
_60days >= .10,"#FFC7CE",
__61days <=.04, "C6EFCE",
__61days >= .05,"#FFC7CE",
_Blank, "#FFC7CE")

This is putting the colors into the visual but not correctly. It currently gives me the following output:
image.png
I have played around with the order of the Switch statement, but it either gives me the top as green and the rest red, or the top red and the rest green. Nothing according to the percentages. I am using "show as percent of column total" to get the percentages. 

Secondly, I need to add into that DAX statement whether or not it is considered Backlog or Closeout, since the reference targets are different depending on the group. 

To sum up, I need to use conditional formatting to show red or green based on whether it's hitting the specified reference target and backlog or closeout. Is this possible? 

I am no PBi expert, so please bear with me. I appreciate any advice. Thanks!

BONUS POINTS - If you can figure out how to make the blank cells either say N/A, 0.0% or grayed out? 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper III
Helper III

Re: Using DAX for Conditional Formatting

Sorry I didn't get a chance to put up some sample data. I was able to figure it out.  
First I needed a measure to get the values I needed:

          % of Total Workorders =
          VAR _FilterCount = COUNTROWS('workorder')
         VAR _AllCount = CALCULATE(COUNTROWS(workorder),ALLSELECTED(workorder))

         RETURN

         DIVIDE(_FilterCount,_AllCount)

Then I used this measure to place in the Conditional Formatting Field Value:
               
% of Total Workorder Color =
VAR _30DaysBL = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="Within 30 Days",SELECTEDVALUE(workorder[Backlog Status Group])="Backlog"),True,False)
VAR _30DaysCO = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="Within 30 Days",SELECTEDVALUE(workorder[Backlog Status Group])="Closeout"),True,False)
VAR _31DaysBL = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="31 - 60 Days",SELECTEDVALUE(workorder[Backlog Status Group])="Backlog"),True,False)
VAR _31DaysCO = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="31 - 60 Days",SELECTEDVALUE(workorder[Backlog Status Group])="Closeout"),True,False)
VAR _61DaysBL = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="61+ Days",SELECTEDVALUE(workorder[Backlog Status Group])="Backlog"),True,False)
VAR _61DaysCO = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="61+ Days",SELECTEDVALUE(workorder[Backlog Status Group])="Closeout"),True,False)

RETURN

SWITCH(
TRUE(),
_61DaysBL && [% of Total Workorders] <=.049, "#C6EFCE",--Green
_61DaysBL && [% of Total Workorders] >= .050,"#FFC7CE",--Red
_31DaysBL && [% of Total Workorders] <=.099, "#C6EFCE",
_31DaysBL && [% of Total Workorders] >= .100,"#FFC7CE",
_30DaysBL && [% of Total Workorders] >= .850,"#C6EFCE",
_30DaysBL && [% of Total Workorders] <=.849, "#FFC7CE",

_61DaysCO && [% of Total Workorders] <=.300, "#C6EFCE",
_61DaysCO && [% of Total Workorders] >= .319,"#FFC7CE",
_31DaysCO && [% of Total Workorders] <=.700, "#FFC7CE",
_31DaysCO && [% of Total Workorders] >= .719,"#C6EFCEE",
_30DaysCO && [% of Total Workorders] >= .719,"#C6EFCE",
_30DaysCO && [% of Total Workorders] <=.700, "#FFC7CE",
"#FFF000")--Yellow

Thank you for your attempted assistance. 


View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: Using DAX for Conditional Formatting

Where are you entering/using this DAX? I was unaware you could control the colors of cells directly with a measure.  Could you share an example .pbix of your current setup? Even if you have to fake some data, it would be very useful.

 

For the bonus point question, what measure(s) are you currently using to fill in this table? Usually just adding +0 to the measure will add a zero instead of a blank to a table.

Highlighted
Helper III
Helper III

Re: Using DAX for Conditional Formatting

Hi Cmcmahan, In order to use a measure for conditional formatting, you need to go to the conditional formatting within the formating section of your visual. From there you want to choose Field Value, and then place the measure you are using in the Based on Field. See below:image.png

 

Hope this helps. 

Highlighted
Super User III
Super User III

Re: Using DAX for Conditional Formatting

AHH, that's how that is supposed to work.  I've always used rules, to format fields.  

 

Could you share some sample data that we can copy/paste? Scrub names/proprietary info if you need to, but figuring out issues (especially with aggregations) is always MUCH easier with a test sample of raw data.

 

If not, let me know, and I can try and fake up some data that will probably(?) behave the same.

Highlighted
Helper III
Helper III

Re: Using DAX for Conditional Formatting

Sorry I didn't get a chance to put up some sample data. I was able to figure it out.  
First I needed a measure to get the values I needed:

          % of Total Workorders =
          VAR _FilterCount = COUNTROWS('workorder')
         VAR _AllCount = CALCULATE(COUNTROWS(workorder),ALLSELECTED(workorder))

         RETURN

         DIVIDE(_FilterCount,_AllCount)

Then I used this measure to place in the Conditional Formatting Field Value:
               
% of Total Workorder Color =
VAR _30DaysBL = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="Within 30 Days",SELECTEDVALUE(workorder[Backlog Status Group])="Backlog"),True,False)
VAR _30DaysCO = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="Within 30 Days",SELECTEDVALUE(workorder[Backlog Status Group])="Closeout"),True,False)
VAR _31DaysBL = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="31 - 60 Days",SELECTEDVALUE(workorder[Backlog Status Group])="Backlog"),True,False)
VAR _31DaysCO = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="31 - 60 Days",SELECTEDVALUE(workorder[Backlog Status Group])="Closeout"),True,False)
VAR _61DaysBL = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="61+ Days",SELECTEDVALUE(workorder[Backlog Status Group])="Backlog"),True,False)
VAR _61DaysCO = IF(AND(SELECTEDVALUE(workorder[Backlog Timeframe])="61+ Days",SELECTEDVALUE(workorder[Backlog Status Group])="Closeout"),True,False)

RETURN

SWITCH(
TRUE(),
_61DaysBL && [% of Total Workorders] <=.049, "#C6EFCE",--Green
_61DaysBL && [% of Total Workorders] >= .050,"#FFC7CE",--Red
_31DaysBL && [% of Total Workorders] <=.099, "#C6EFCE",
_31DaysBL && [% of Total Workorders] >= .100,"#FFC7CE",
_30DaysBL && [% of Total Workorders] >= .850,"#C6EFCE",
_30DaysBL && [% of Total Workorders] <=.849, "#FFC7CE",

_61DaysCO && [% of Total Workorders] <=.300, "#C6EFCE",
_61DaysCO && [% of Total Workorders] >= .319,"#FFC7CE",
_31DaysCO && [% of Total Workorders] <=.700, "#FFC7CE",
_31DaysCO && [% of Total Workorders] >= .719,"#C6EFCEE",
_30DaysCO && [% of Total Workorders] >= .719,"#C6EFCE",
_30DaysCO && [% of Total Workorders] <=.700, "#FFC7CE",
"#FFF000")--Yellow

Thank you for your attempted assistance. 


View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Top Solution Authors