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

 

Thank you

 

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,

5 Replies

  • HenryJS's avatar
    HenryJS
    Post Prodigy
    6 years ago

    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's avatar
      AilleryO
      Memorable Member
      6 years ago

      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's avatar
        AilleryO
        Memorable Member
        6 years ago

        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.

  • ibarrau's avatar
    ibarrau
    Super User
    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,