Forum Discussion

olimilo's avatar
olimilo
Post Prodigy
9 years ago

NETWORKDAYS.INTL with Country as context

So, I would like to compute for the number of working days with the Country as a contextual filter. As some of you might know, weekends vary on several countries (eg: Middle East countries like Jorda...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

olimilo

 

Hi,

 

First, we need to change one relationship.  Actually Holidays and Weekdays are the features of country. The relationship between Holidays and Dates isn’t proper. Let’s delete the relationship Holidays—Dates and create a new one. See the details in the picture 1. Change the Cross Filter Direction into “both” at the same time.

Second, create several measures.

According to test, you formula of NumberOfHolidays doesn’t work. Try this one please.

 

NumberOfHolidays =
COUNTROWS (
    FILTER (
        Holidays,
        Holidays[Country] = MIN ( 'Active Projects'[Country] )
            && Holidays[Date] >= MIN ( 'Active Projects'[CompleteByStartDate] )
            && Holidays[Date] <= MIN ( 'Active Projects'[CompleteByEndDate] )
            && Holidays[IsWeekend] = "False"
    )
)

 

AllWorkdays =
CALCULATE (
    COUNT ( 'Dates'[Date] ),
    FILTER (
        ALL ( 'Dates' ),
        'Dates'[Date] >= MIN ( 'Active Projects'[CompleteByStartDate] )
            && 'Dates'[Date] <= MIN ( 'Active Projects'[CompleteByEndDate] )
            && (
                NOT WEEKDAY ( 'Dates'[Date], 1 )
                    IN {
                            VALUE ( LEFT ( MIN ( Weekdays[WeekendNum] ), 1 ) ),
                            VALUE ( RIGHT ( MIN ( Weekdays[WeekendNum] ), 1 ) ) }
            )
    )
)
    - [NumberOfHolidays]

 

Third, something about your errors. The reason is clear. We can use Min or Max to solve this. Because there is only one value there due to filter context. (The Client in Active Projects could be unique or some other field could be).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

  • olimilo's avatar
    olimilo
    Post Prodigy
    9 years ago

    v-jiascu-msft

     

    Hi Dale! Thank you for the detailed explanation! I've since updated my data structure to somewhat reflect what you've demonstrated on your post:

     

    I am unsure if this is how it should be (with the relationship directions and the dimensions/facts (do let me know if this needs to be corrected).

     

    I tried this DAX code as a measure and was getting a The operation was cancelled because of locking conflicts error. If I don't catch that error, I just end up with a vis that seems to be eternally loading. Do you know what could the cause be?

     

    Days Elapsed (Window) = 
	VAR DaysElapsed = 
		CALCULATE(
			COUNT('Dates'[Date]),
			FILTER(
				ALL('Dates'),
				'Dates'[Date] > MIN('Active Projects'[CompleteByEndDate]) &&
				'Dates'[Date] <= TODAY() &&
				(
					NOT WEEKDAY('Dates'[Date], 1)
						IN {
							VALUE(LEFT(MIN(Weekdays[WeekendNum]), 1)),
							VALUE(RIGHT(MIN(Weekdays[WeekendNum]), 1))
						}
				)
			)
		)

	RETURN
		SWITCH(
			TRUE(),
			MIN('Active Projects'[CompleteByEndDate]) < TODAY(), DaysElapsed + 0,
			MIN('Active Projects'[CompleteByEndDate]) > TODAY(), -1 * DaysElapsed + 0
		)
    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      9 years ago

      olimilo

       

      Hi,

       

      It’s very strange. I can use this formula without any modification. It seems the error message happened in the database. Do you use a DirectQuery mode with a database? Did the error show when you finished input or used in the report?https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ec842da4-2cfa-4f2b-95e3-8627ba18f622/the-operation-was-cancelled-because-of-locking-conflicts?forum=sqlanalysisservices

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Best Regards!

      Dale

      • olimilo's avatar
        olimilo
        Post Prodigy
        9 years ago

        That's the first page I went to when I went looking for the cause of the error. I'm not directly querying from the database. I'm using an Excel spreadsheet for all my tables. The Dates table has at least 5000 rows x 10 rows while the Active Projects table has on average 1500 rows x ~35 columns. Not sure if that contributes to the error though.

         

        I did notice the slowdown when selecting entries from China (the one where we have the most data from). I'm getting values if I use it as a column instead though (although I'm getting an extra "day" in the count, eg: from 5/12 to 5/29 Israel - Fri/Sat weekends, the Days Elapsed should be 12 days, but I'm getting 14.

         

        Days Elapsed (Window) = 
	VAR DaysElapsed = 
		CALCULATE(
			COUNTROWS('Dates'),
			FILTER(
				ALL('Dates'),
				'Dates'[Date] > 'Active Projects'[CompleteByEndDate] &&
				'Dates'[Date] <= TODAY() &&
				(
					NOT WEEKDAY('Dates'[Date], 1)
						IN {
							VALUE(LEFT(MIN(Weekdays[WeekendNum]), 1)),
							VALUE(RIGHT(MIN(Weekdays[WeekendNum]), 1))
						}
				)
			)
		)

	RETURN DaysElapsed + 0