Forum Discussion

HenryJS's avatar
HenryJS
Post Prodigy
6 years ago
Solved

New Column: DAX Query IF Dates & Values

Hi all,   How can I add a column that returns "Yes" if the following statements are true: CSCS is blank OR CSCS is in the next 1 Month OR Passport is blank OR Passportin the next 1 Month OR R...
Data Protection
Need Help
Show and Tell
Tips and Tricks
  • ibarrau's avatar
    ibarrau
    6 years ago

    Hi, ok this will be an interesting column condition. SWITCH is a great option when you have only one value to compare with a lot of values but in your case you have several values to check so I think it won't work that easy.

     

    You can go checking each if response and joining another IF like this:

     

     

     

    NewColumn =
VAR next_month = 
Date ( 
    YEAR ( EDATE ( Today() , +1 ) ) , 
    MONTH ( EDATE( Today() , +1 ) ) , 
    1
)
RETURN
IF (
    OR( 
        ISBLANK( Table[CSCS] ),
        AND (
            YEAR(Table[CSCS]) = YEAR(next_month),
            MONTH(Tale[CSCS]) = MONTH(next_month)
        )
    ),
    IF (
        OR( 
            ISBLANK( Table[Passport] ),
            AND (
                YEAR(Table[Passport]) = YEAR(next_month),
                MONTH(Tale[Passport]) = MONTH(next_month)
            )
        ),
        IF ( AND( Table[Reference1] = "No" , Table[Reference2] = "No" ),
            TRUE(),
            FALSE()
        ),
        FALSE()
    ),
    FALSE()
)

     

     

     

     Or you can try to create all in one condition (this is smaller but it's more risky to make mistakes)

     

     

     

    NewColumn =
VAR next_month = 
Date ( 
    YEAR ( EDATE ( Today() , +1 ) ) , 
    MONTH ( EDATE( Today() , +1 ) ) , 
    1
)
RETURN
IF (
    OR( 
        ISBLANK( Table[CSCS] ),
        AND (
            YEAR(Table[CSCS]) = YEAR(next_month),
            MONTH(Tale[CSCS]) = MONTH(next_month)
        )
    )
    &&
    OR( 
        ISBLANK( Table[Passport] ),
        AND (
            YEAR(Table[Passport]) = YEAR(next_month),
            MONTH(Tale[Passport]) = MONTH(next_month)
        )
    )
    &&
    AND( 
    	Table[Reference1] = "No" , Table[Reference2] = "No" 
    ),
    TRUE(),
    FALSE()
)

     

     

     

     

    Hope this works,

    Regards,