New Column: DAX Query IF Dates & Values

[HenryJS]

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 Reference 1 is "No"
• OR Reference 2 is "No"

Thank you

[ibarrau · Accepted solution]

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,

[HenryJS]

@amitchandak can you help? I know I need to use a Switch and || but I need to add in date and text functions?

Thanks

[AilleryO]

Hi,

I assume you have already a table Date ?

If so why not adding to your Calendar table, a column with a YES/NO ou 1/0 flag, testing if it's in the next month.

Here is an example, close to your needs :

https://community.powerbi.com/t5/Desktop/Measure-for-Current-Month/td-p/91531

Once you have that, you do not need any more a filter on your date but just on the calculated column (filtering Yes or 1).

That should solve your problem for testing if its in next month.

[AilleryO]

For your blank test,

you have the ISBLANK function,

and for the text :

[MyField]="My Text" or [MyField]=1

Is that helping ? Or could you give more details.

[AilleryO]

Hi,

You could use a SWITCH function like in this example :

https://community.powerbi.com/t5/Desktop/Help-with-SWITCH-TRUE-with-multiple-items-to-evaluate/td-p/...

And for your AND statement use &&

and for the OR use ||.

This should do the trick