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