Forum Discussion

PowerBINewbie_1's avatar
PowerBINewbie_1
Helper I
8 years ago

Nested DAX

Hi,   I'm new to the DAX community and I'm completely perplexed about how to convert my excel formulas into a DAX function that works. I have about 30 columns in my worksheet that deal with dates. ...
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi PowerBINewbie_1,

 

 1.) =IF([SharePointColumnA]="",0,

 

IF([SharePointColumnD]="", CALCULATE(SUM(DateTable[WORKDAY]),DATESBETWEEN(DateTable[Date],'SharePointList'[SharePointColumnA],TODAY()), 

 

CALCULATE(SUM(DateTable[WORKDAY]),DATESBETWEEN(DateTable[Date],'SharePointList'[SharePointColumnA],'SharePointList'[SharePointColumnD])))

 

ERROR MESSAGE: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed. 

 

Column1 =
IF (
    [SharePointColumnA] = "",
    0,
    IF (
        [SharePointColumnD] = "",
        CALCULATE (
            SUM ( DateTable[WORKDAY] ),
            FILTER (
                DateTable,
                DateTable[Date] >= 'SharePointList'[SharePointColumnA]
                    && DateTable[Date] <= TODAY ()
            )
        ),
        CALCULATE (
            SUM ( DateTable[WORKDAY] ),
            FILTER (
                DateTable,
                DateTable[Date] >= 'SharePointList'[SharePointColumnA]
                    && DateTable[Date] <= 'SharePointList'[SharePointColumnD]
            )
        )
    )
)

 

2.) =IF([SharePointColumnHold Start Date_1]="",0,

IF([SharePointColumnHold Start Date_1]>[SharePointColumnD],0,

IF('SharePointList'[SharePointColumnHold End Date_1]>[SharePointColumnD],CALCULATE(SUM(DateTable[WORKDAY], DATESBETWEEN (DateTable[Date], 'SharePointList'[SharePointColumnHold Start Date_1],'SharePointList'[SharePointColumnD],

IF('SharePointList'[SharePointColumnHold End Date_1]="",CALCULATE(SUM(DateTable[WORKDAY],DATESBETWEEN(DateTable[Date], 'SharePoint List'[SharePointColumnHold Start Date_1]), TODAY()),

CALCULATE(SUM(DateTable[WORKDAY]),DATESBETWEEN(DateTable[Date],'SharePointList'[SharePointColumnHold Start Date_1],'SharePointList'[SharePointColumnHold End Date_1])))

 

ERROR MESSGAE: Too few arguments were passed to the DATESBETWEEN function. The minimum argument count for the function is 3.

 

 

In this formula, you missed Parentheses ')'. 

2.) =IF([SharePointColumnHold Start Date_1]="",0,

IF([SharePointColumnHold Start Date_1]>[SharePointColumnD],0,

IF('SharePointList'[SharePointColumnHold End Date_1]>[SharePointColumnD],CALCULATE(SUM(DateTable[WORKDAY], DATESBETWEEN (DateTable[Date], 'SharePointList'[SharePointColumnHold Start Date_1],'SharePointList'[SharePointColumnD],

IF('SharePointList'[SharePointColumnHold End Date_1]="",CALCULATE(SUM(DateTable[WORKDAY]),DATESBETWEEN(DateTable[Date], 'SharePoint List'[SharePointColumnHold Start Date_1]), TODAY()),

CALCULATE(SUM(DateTable[WORKDAY]),DATESBETWEEN(DateTable[Date],'SharePointList'[SharePointColumnHold Start Date_1],'SharePointList'[SharePointColumnHold End Date_1])))

 

3. )=IF([SharePointColumnReferral Date]="",0,

IF([SharePointColumnReferral Date]>=TODAY(),0,
IF([SharePointColumnC]>TODAY(),(TODAY()-[SharePointColumnReferral Date])+1,
IF([SharePointColumnC]="",(TODAY()-[SharePointColumnReferral Date])+1,
([SharePointColumnC]-[SharePointColumnReferral Date])+1))))

 

ERROR MESSAGE: DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values. 

Please check the date type of column [SharePointColumnReferral Date] and [SharePointColumnC] to make sure they are formatted as date. Also, you'd better changed the formula similar to

=
IF (
    [SharePointColumnReferral Date] = BLANK (),
    0,
    IF (
        [SharePointColumnReferral Date] >= TODAY (),
        0,
        IF (
            [SharePointColumnC] > TODAY (),
            ( TODAY () - [SharePointColumnReferral Date] )
                + 1,
            IF (
                [SharePointColumnC] = BLANK (),
                ( TODAY () - [SharePointColumnReferral Date] )
                    + 1,
                ( [SharePointColumnC] - [SharePointColumnReferral Date] )
                    + 1
            )
        )
    )
)

Regards,

Yuliana Gu