Forum Discussion

craigL01's avatar
craigL01
New Member
4 years ago
Solved

Multiple IF AND DAX statement not working

I have written the following DAX . Its basically looking at 3 date fields on a sql AX database so 01/01/1900 is no date. The first IF is checking there is a date in LETTERSENTDATE01 and there is not ...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi craigL01 
    You need to use proper date data type

    Visits =
IF (
    PROJTABLE[MXMSURVEYLETTERSENTDATE01] > DATE ( 1900, 01, 01 )
        && PROJTABLE[MXMSURVEYLETTERSENTDATE02] = DATE ( 1900, 01, 01 ),
    1,
    IF (
        PROJTABLE[MXMSURVEYLETTERSENTDATE01] > DATE ( 1900, 01, 01 )
            && PROJTABLE[MXMSURVEYLETTERSENTDATE02] > DATE ( 1900, 01, 01 )
            && PROJTABLE[MXMSURVEYLETTERSENTDATE03] = DATE ( 1900, 01, 01 ),
        2,
        IF (
            PROJTABLE[MXMSURVEYLETTERSENTDATE01] > DATE ( 1900, 01, 01 )
                && PROJTABLE[MXMSURVEYLETTERSENTDATE02] > DATE ( 1900, 01, 01 )
                && PROJTABLE[MXMSURVEYLETTERSENTDATE03] > DATE ( 1900, 01, 01 ),
            3,
            0
        )
    )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi craigL01 
You need to use proper date data type

Visits =
IF (
    PROJTABLE[MXMSURVEYLETTERSENTDATE01] > DATE ( 1900, 01, 01 )
        && PROJTABLE[MXMSURVEYLETTERSENTDATE02] = DATE ( 1900, 01, 01 ),
    1,
    IF (
        PROJTABLE[MXMSURVEYLETTERSENTDATE01] > DATE ( 1900, 01, 01 )
            && PROJTABLE[MXMSURVEYLETTERSENTDATE02] > DATE ( 1900, 01, 01 )
            && PROJTABLE[MXMSURVEYLETTERSENTDATE03] = DATE ( 1900, 01, 01 ),
        2,
        IF (
            PROJTABLE[MXMSURVEYLETTERSENTDATE01] > DATE ( 1900, 01, 01 )
                && PROJTABLE[MXMSURVEYLETTERSENTDATE02] > DATE ( 1900, 01, 01 )
                && PROJTABLE[MXMSURVEYLETTERSENTDATE03] > DATE ( 1900, 01, 01 ),
            3,
            0
        )
    )
)
  • craigL01's avatar
    craigL01
    New Member
    4 years ago

    Brilliant thanks works fine now.