topic Need help with combing If or and if and statements in DAX Commands and Tips

Need help with combing If or and if and statements

james_pease — Mon, 26 Sep 2022 15:45:37 GMT

Hello all, I am trying to pass multiple tests that return "yes." The first test is to see if an employees last pay day is greater than 21 days, the second is to test wether their last pay date is blank and if so, return yes if their hire date is greater than 21 days from today.

Here is what I have so far:

Active No Hours = IF(DATEDIFF('ADP Active Employees'[Last Pay Date], TODAY(), DAY)>21, "Yes")

||IF(AND(LEN('ADP Active Employees'[Last Pay Date]) <0,
DATEDIFF('ADP Active Employees'[Last Pay Date], TODAY(), DAY)>21),"Yes")

I have tried using ISBLANK with "If and" but it kept saying two few arguments were passed, which is what led me to try "If and" with a Len <0. Thank you in advance!

Re: Need help with combing If or and if and statements

james_pease — Mon, 26 Sep 2022 16:00:29 GMT

I have also tried the following:

IF(OR(DATEDIFF('ADP Active Employees'[Last Pay Date], TODAY(), DAY)>21,

IF(AND(LEN('ADP Active Employees'[Last Pay Date]) <0,

DATEDIFF('ADP Active Employees'[Last Pay Date], TODAY(), DAY)>21),"Yes")), "Yes")

The second arguement isnt being passed because its using it as the true statement versus a second argument.

But the results are less than what I am expecting, the

Re: Need help with combing If or and if and statements

tamerj1 — Mon, 26 Sep 2022 16:01:49 GMT

Hi james_pease

please try

Active No Hours = IF ( DATEDIFF ( 'ADP Active Employees'[Last Pay Date], TODAY (), DAY ) > 21 || AND ( LEN ( 'ADP Active Employees'[Last Pay Date] ) < 0, DATEDIFF ( 'ADP Active Employees'[Last Pay Date], TODAY (), DAY ) > 21 ), "Yes" )

Re: Need help with combing If or and if and statements

james_pease — Mon, 26 Sep 2022 16:47:34 GMT

Thank you, but for some reason its not reutrning the employees with an blank last pay date (empty cell). Its passing the first arguement fine, but for some reason, its not testing if the last pay date is blank, then return yes if hire date is > than 21 days from today.

Here is a screenshot, I blacked out names for privacy. Notice how it is not returing a "yes" for those without a last pay date and their hire date is > today + 21 days?

AHHH so sorry, I needed to change the second clause to

DATEDIFF ( 'ADP Active Employees'[Hire/Rehire Date], TODAY (), DAY ) > 21

Works like a charm!

Re: Need help with combing If or and if and statements

tamerj1 — Mon, 26 Sep 2022 16:16:43 GMT

james_pease

Please try

Active No Hours = IF ( DATEDIFF ( 'ADP Active Employees'[Last Pay Date], TODAY (), DAY ) > 21 || AND ( ISBLANK ( 'ADP Active Employees'[Last Pay Date] ), DATEDIFF ( 'ADP Active Employees'[Last Pay Date], TODAY (), DAY ) > 21 ), "Yes" )

Re: Need help with combing If or and if and statements

james_pease — Mon, 26 Sep 2022 16:42:28 GMT

Sadly, same result:

Notice how there are 1501 blanks, I am expecting several to say "yes" in the 'Active No Hours column'