Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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. I'm looking to find the business days between dates. This will exclude weekends and federal government holidays.

 

A variation of the excel formulas that I have are below:

 

1.) =IF([@[SharePointColumnA]]="",0,
IF([@[SharePointColumnD]]="",NETWORKDAYS([@[SharePointColumnA]],Current_Date,List.Holidays),
NETWORKDAYS([@[SharePointColumnA]],[@[SharePointColumnD]],List.Holidays)))

 

2.) =IF([@[SharePointColumnHold Start Date_1]]="",0,
IF([@[SharePointcolumnHold Start Date_1]]>[@[SharePointColumnD]],0,
IF([@[SharePointColumnHold End Date_1]]>[@[SharePointColumnD]],NETWORKDAYS([@[SharePointColumnHold Start Date_1]],[@[SharePointColumnD]],List.Holidays),
IF([@[SharePointColumnHold End Date_1]]="",NETWORKDAYS([@[SharePointColumnHold Start Date_1]],Current_Date,List.Holidays),
NETWORKDAYS([@[SharePointColumnHold Start Date_1]],[@[SharePointColumnHold End Date_1]],List.Holidays)))))

 

3.) =IF([@[SharePointColumnReferral Date]]="",0,
IF([@[SharePointColumnReferral Date]]>=Current_Date,0,
IF([@[SharePointColumnC]]>Current_Date,(Current_Date-[@[SharePointColumnReferral Date]])+1,
IF([@[SharePointColumnC]]="",(Current_Date-[@[SharePointColumnReferral Date]])+1,
([@[SharePointColumnC]]-[@[SharePointColumnReferral Date]])+1))))

 

 

When I tried to copy and paste these formulas into PowerBI, these fields all greyed out. Additionally, the NETWORKDAYS function is not recognized in PowerBI. Therefore, I had to do some research to find out how to create a NETWORKDAY function within Power BI. After I followed posts online. I had to modify the above formulas accordingly. These new formulas are:

 

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.

 

 

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.

 

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.

 

 

Is there someone more advanced with DAX who is willing to provide the proper syntax and formulas to help replicate the excel formulas that I listed above?  If at all possible, could you also help me identify what I did incorrectly and  provide references that are helpful for beginners? Ultimately, I'd like to look at these examples, review the solutions, read the referenced material and properly construct the other 27 columns that mirror the same logic above.

 

Thanks! 

 

5 REPLIES 5
v-yulgu-msft
Employee
Employee

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

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

Use my Net Work Days Quick Measure:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hello Greg,

 

Thank you. You're in the ballpark with this solution. However, I'm looking to capture the 0's and to use the current date when a date is not provided. How would you modify your solution to do that?

 

Thanks!

PBN

Not sure what you mean by capture the zeros, but for the second one you can just use an VAR that has an IF statement with an ISBLANK to check if there is no date value provided and in that case set the variable equal to TODAY().


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi,

 

In my formulas above. I'm saying, if a certain column is blank then output a "0". Otherwise, perform calculations that will give me a total of the business days between two dates. Your solution provides a way to only total the business days between dates. However, there are multiple factors that I am trying to address here. 

 

1. If the beginning date is empty. Output a "0".

2. If the ending date is empty use TODAY() date. To calcualte total business days

 

3. Then ultimately sum the total business days between two dates if two dates are provided.

4. Understand the proper way to nest IF statements that have more than 3 conditions.

 

In my examples above, I have nested if statments that are complicated because I have to nest them properly to perform the calculations at hand. Then use the CALCULATE SUM to replace the network days excel function. Thus it gets tricky. As a newbie, examples would work best.

 

Thanks for your input though.

 

Regards,

PBN 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.