Forum Discussion

byWing's avatar
byWing
Helper I
2 years ago
Solved

DAX for checking 2 dates

Hi All,

 

I find myself struggling with DAX and appreciate some insights on how to write this DAX pls:

 

End goal: To set up conditional formatting which will flag an item if the [Due Date] is pass today, AND the [Corrective Action Completion Date] is blank, (which means the engineer has not completed the work yet.)

 


My error: 

 

Something must be wrong with [Corrective Action Completion Date], but I cannot figure out why.

 

Any insights, advice will be much appreciated pls.

Thank you so much~

Need Help
  • Sergii24's avatar
    Sergii24
    2 years ago

    Hi byWing, the answer is right there in your error message: ISBLANK() requires you to pass a single value as an argument, not a table. You can use SELECTEDVALUE() or any other aggregation to obtain a single value. This should work fine:

    Overdue = 
   if(
      AND(
         MAX( [Due Date] )>TODAY(),
         ISBLANK( SELECTEDVALUE( sa_RPN_Reduction[corrective action completion date] ) )
      ),
      1,
      0
   )

2 Replies

  • Sergii24's avatar
    Sergii24
    Super User
    2 years ago

    Hi byWing, the answer is right there in your error message: ISBLANK() requires you to pass a single value as an argument, not a table. You can use SELECTEDVALUE() or any other aggregation to obtain a single value. This should work fine:

    Overdue = 
   if(
      AND(
         MAX( [Due Date] )>TODAY(),
         ISBLANK( SELECTEDVALUE( sa_RPN_Reduction[corrective action completion date] ) )
      ),
      1,
      0
   )
    • byWing's avatar
      byWing
      Helper I
      2 years ago

      Thank you Sergii24  for your reply. Your reply helped me a lot and this is the final one I used:

       

      Overdue =
         if(
            AND(
               MAX(SA_RPN_Reduction[Due Date])<TODAY(),
               ISBLANK( SELECTEDVALUE(SA_RPN_Reduction[Corrective Action Completion Date] ) )
            ),
            1,
            0
         )
       
      Yay!