Forum Discussion

olimilo's avatar
olimilo
Post Prodigy
9 years ago

NETWORKDAYS with Holidays from different countries

Hi guys, this is a bit of a challenge for me and I'm hoping someone can help lead me in the right direction.   I have a Records table that I need to check for the # of working days from StartDate t...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks Joe,

I'm OK with getting public holidays for 1 country via an API and then merging it with my Date Dimension table to create an "Is Holiday" type column.  

The issue I have is I'm now building a Date Dimension table in a Data Warehouse that will be used in multiple countries and not sure how that will work in practice.

Thinking out loud, I'm thinking we would have an "Is Holiday" column in the DimDate table for each country.  As long as reports are filtered to 1 country through RLS or explicitly we could have dynamic measures in the dataset file doing something like:

 

 

Holiday Days in Timeframe = 

var _country = SELECTEDVALUE('Dim Country') 
var _holiday_column = "'DIM Date'[Is Holiday " & _country & "]" 
//(Not sure the above is even possible in DAX) 

var _result = 

CALCULATE( 
DISTINCTCOUNTNOBLANK(_holiday_column), 
FILTER('DIM Date', 'DIM Date'[Date] >= Records[CompleteByStartDate] 
&& 'DIM Date'[Date] < Records[CompleteByEndDate])) 

return _result

 

 



JoeBarry's avatar
JoeBarry
Solution Sage
3 years ago

I think it would be easier to just create a Public holiday table with Date, Country, CountryID, HolidayName columns. 

 

Adding a column to the date table can get messy, whereas, if you have one table, more countries can be added later without adding more columns.

 

The 2nd solution I created would then be useful. You can do RLS then on a DIM_Country table which has a one to many relationship with both the Holiday Table and the Records table

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks Joe. 
    You're right,  using a DIM_Holiday_Country table (that will only ever have 1 country selected; either through a slicer or via RLS) that filters our Dim_Holidays (but not our Fact table) is our best shout I think.
    That way, the user (or RLS) defines which country we're using for our Public Holidays in any measures related to working days.  We can then have Executive level report users still being able to view all countries in the report because we're not filtering the Fact Table.

    I'm then going to create a calculated Is_Holiday column using DAX (LOOKUPVALUE) on my DimDate table to bring in just the filtered holidays in my DimHolidays table.

    Funnily enough I came to the same conclusion as you after a long conversation with ChatGPT. 🙂 

    Thanks for your help,
    Clem