Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional Formatting using DAX

Hi,

 

I'm trying to write a Dax measure for conditional formatting to occur  in the below table as follows - 

 

  • Staff ID's that have a "Yes" in "LSL Accrued >2 Years", blank in "LSL Accrued >3" and if the figure in 'accrued leave (days) is different to the figure in 'Leave booked' show as Orange 

 

  • Staff ID's that have a Yes in both LSL Accrued >2 Years and LSL Accrued >3 Years and have a figure in accrued leave (days) that is different to leave booked show as Red.

 

Table would look like this - 

Staff IDLSL Accrued >2LSL Accrued >3Leave bookedAccrued Leave
1233456Yes 0.010.0

1324644

YesYes10.010.0
1238751  5.010.0
1325482    
5484184Yes 14.014.0
484564    
128796YesYes10.012.0
1254844YesYes22.022.0
1258796Yes 5.020.0
123846YesYes20.035.0
58974  7.014.0
123658Yes 0.025.0

 

Would really appreciate any help! Pragati11 

Thank you ğŸ™‚

Conditio
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    There are some mistakes in VahidDM 's code. According to your statement, I know there should be a same condition that the figure in 'accrued leave (days) is different to the figure in 'Leave booked' in code.

     

    Color Staff ID = 
VAR _LB =
    SUM ( 'Table'[Leave booked] )
VAR _AB =
    SUM ( 'Table'[Accrued Leave] )
VAR _LSL2 =
    MAX ( 'Table'[LSL Accrued >2] )
VAR _LSL3 =
    MAX ( 'Table'[LSL Accrued >3] )
RETURN
    IF (
        _LB <> _AB,
        IF (
            _LSL2 = "Yes"
                && _LSL3 = BLANK (),
            "Orange",
            IF ( _LSL2 = "Yes" && _LSL3 = "Yes", "Red" )
        )
    )

     

    Add this measure in background color and choose Field value. Result is as below.  

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    There are some mistakes in VahidDM 's code. According to your statement, I know there should be a same condition that the figure in 'accrued leave (days) is different to the figure in 'Leave booked' in code.

     

    Color Staff ID = 
VAR _LB =
    SUM ( 'Table'[Leave booked] )
VAR _AB =
    SUM ( 'Table'[Accrued Leave] )
VAR _LSL2 =
    MAX ( 'Table'[LSL Accrued >2] )
VAR _LSL3 =
    MAX ( 'Table'[LSL Accrued >3] )
RETURN
    IF (
        _LB <> _AB,
        IF (
            _LSL2 = "Yes"
                && _LSL3 = BLANK (),
            "Orange",
            IF ( _LSL2 = "Yes" && _LSL3 = "Yes", "Red" )
        )
    )

     

    Add this measure in background color and choose Field value. Result is as below.  

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Anonymous I've just noticed that there are some strange anomalies occuring with the conditional formatting - the dax measure doesn't appear to be recognising that some of the Accrued LSL values are the same at the Bookings values.  I tried adjusting the measure to Bookings < Accrued but that didn't seem to work either.  

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        I think the logic of my measure should be correct. Firstly, it will compare 'accrued leave (days) with 'Leave booked'. Only 'accrued leave (days) is different to the figure in 'Leave booked', it will go to next calculate, or the color is blank.

        This may caused by your data model. Please share a sample file with us. This will make us easier to find the solution.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you, this has worked perfectly! ğŸ˜»

      Is there a way to conduct a count of how many staff ID's meet the conditional formatting ciriteria?

       

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi Anonymous 

     

    Try this code and use that in the conditional formatting:

    Color Staff ID = 
Var _LB = max('Table'[Leave booked])
Var _AB = max('Table'[Accrued Leave])
Var _LSL2 = max('Table'[LSL Accrued >2])
Var _LSL3 = max('Table'[LSL Accrued >3]) 
return
if(_AB=_LB&&_LSL2="Yes"&&_LSL3=blank(),"#E66C37",if(_AB<>_LB&&_LSL2="Yes"&&_LSL3="Yes","#F61223"))

     

    Output:

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/