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 a date in LETTERSENTDATE02 result being 1, then the next IF checks there are dates on 01 and 02 and not in 03 result being 2 and the final IF chaecks there are dates in 01, 02 and 03 result being 3.

 

Visits = IF(PROJTABLE[MXMSURVEYLETTERSENTDATE01]>01/01/1900 && PROJTABLE[MXMSURVEYLETTERSENTDATE02]=01/01/1900,1,
IF(PROJTABLE[MXMSURVEYLETTERSENTDATE01]>01/01/1900 && PROJTABLE[MXMSURVEYLETTERSENTDATE02]>01/01/1900 && PROJTABLE[MXMSURVEYLETTERSENTDATE03]=01/01/1900,2,
IF(PROJTABLE[MXMSURVEYLETTERSENTDATE01]>01/01/1900 && PROJTABLE[MXMSURVEYLETTERSENTDATE02]>01/01/1900 && PROJTABLE[MXMSURVEYLETTERSENTDATE03]>01/01/1900,3,0)))
When it runs it marks evety record with 3 totally ignoring the first two if statements
 
i did a quick little data table in BI just to show the field results and values and you can see i have lines that should have a visits of 2 and 1.

 

Appreciate any help you can give

 
 

 

  • 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
        )
    )
)

2 Replies

  • 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
        )
    )
)