Using another table (e.g., holidays) to filter a calendar table

[MJEnnis]

I am trying to use a holiday table to filter a calendar table that already filters other tables and measures. When the applied filter is both directions, the calendar table correctly filters the holiday table. But the filter will not propagate in the other direction. Any suggestions?

This is the calendar table:

Calendar = 
VAR MinYear = YEAR ( MIN ( 'Lista Preventivi e Prenotazioni e Cancellazioni'[DataAcquisizione] ) )
VAR MaxYear = YEAR ( MAX ( 'Lista Preventivi e Prenotazioni e Cancellazioni'[Partenza]) )
RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ),
        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
    ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "WeekNO", WEEKNUM ( [Date], 16 ),
    "WeekDay", IF(WEEKDAY ( [Date], 1 ) = 7, WEEKDAY ( [Date], 1 ) - 6, WEEKDAY ( [Date], 1 ) + 1),
    "StartofWeek", [Date]+1-WEEKDAY([Date]-6)
)

I have added a key to this table with the following calculated column, which looks up the correct key in a third, unrelated table:

Order_ = CALCULATE(MAX(Holidays[order_]), FILTER(Holidays, 'Calendar'[Date] < Holidays[Data fine] &&'Calendar'[Date] >= Holidays[Data inizio]))

Here is one year's worth of data from the Holidays Color table, which I want to use to filter the Calendar table using Order_ as the key (this table was created by duplicating the quers for the Holidays table mentioned above and then adding the color info):

Anno	Festività	Data inizio	Data fine	Commenti	Order	Color Code	Color Code - Copy	Order_
2025	Settimana Pasqua 1	12.Apr.25	19.Apr.25		1	#CCCCCC	CCCCCC	2025001
2025	Settimana Pasqua 2	19.Apr.25	26.Apr.25	Pasqua 20/04  e Coinciveda con Tulipani 1 e Venerdì 25/04	2	#CCCCCC	CCCCCC	2025002
2025	Tuliani 1	19.Apr.25	26.Apr.25	Coincideva con Pasqua 2 e Venerdì 25/04	3	#A0D1FF	A0D1FF	2025003
2025	Tuliani 2	26.Apr.25	03.May.25	Giovedì 01/05	4	#41A4FF	41A4FF	2025004
2025	Tuliani 3	03.May.25	10.May.25		5	#A0D1FF	A0D1FF	2025005
2025	Settimana Ascensione	24.May.25	31.May.25	Giovedì 29/05	6	#AEFDE4	AEFDE4	2025006
2025	Pre-Pentecoste	31.May.25	07.Jun.25	Lunedì 02/06	7	#EFB5B9	EFB5B9	2025007
2025	Pentecoste 1	07.Jun.25	14.Jun.25		8	#DE6A73	DE6A73	2025008
2025	Pentecoste 2	14.Jun.25	21.Jun.25		9	#DE6A73	DE6A73	2025009
2025	Festa Vino 1	27.Sep.25	04.Oct.25	Venerdì 03/10	10	#717BC5	717BC5	2025010
2025	Festa Vino 2	04.Oct.25	11.Oct.25		11	#717BC5	717BC5	2025011
2025	Vacanze Danesi	11.Oct.25	18.Oct.25		12	#F0E199	F0E199	2025012
2025	Halloween 1	25.Oct.25	01.Nov.25	Venerdì 31/10	13	#F5C4AF	F5C4AF	2025013
2025	Halloween 2	01.Nov.25	08.Nov.25		14	#F5C4AF	F5C4AF	2025014

[Greg_Deckler · Accepted solution]

@MJEnnis From your last image, it does look like it is having an effect. Everything other than the 04/04/2015 row is all 0's so something is going on. If you want rows to not show up you could use a Complex Selector. The Complex Selector - Microsoft Fabric Community

[Greg_Deckler]

@MJEnnis I'm not quite sure what you are trying to do here. You can get a list of date values using DISTINCT from the holiday table and use the IN operator to FILTER the dates table. But honestly, I'm not exactly sure what you are trying to do here.

[MJEnnis]

Hi @Greg_Deckler!

I guess I am being intentionally vague. Helping my wife, so the data isn't mine and I cannot share too much of it. 😞

Maybe some images will help?

Below are the three tables in the model with the relationship I want. The relationship was created as described above: many to one, single, unique key, which was added to the calendar table with the above calculated column. Note that the calendar table is related to several other tables not shown and is also involved in several measures, though all but one of those relationships are inactive in the model.



When I filter visuals populated with measures and data from tables related to the calendar table, everything works properly. For example, as shown below, using the week number from the calendar table filters measures/visuals as expected.

Also, if I change the direction of the relationship shown above to both, then the calendar table does filter the holiday colors table, as expected. For instance, highlighting any week that corresponds to the first week of the Easter holiday reduces the legend visual to just that holiday label, as shown here: 



The problem is that the holiday colors table does filter anything. As shown below, removing other filters and selecting the second week of Easter has no effect on the visual or measures. I do not understand why...

[Greg_Deckler · Accepted solution]

@MJEnnis From your last image, it does look like it is having an effect. Everything other than the 04/04/2015 row is all 0's so something is going on. If you want rows to not show up you could use a Complex Selector. The Complex Selector - Microsoft Fabric Community

[MJEnnis · Accepted solution]

Figured it out! Your complex selector post was helpful, but the solution was much simpler in my case. I have a measure that calculates the total number of any given product available on any given calendar day, considering the start and end season for each product. All I had to do was tell my existing measures to return a blank if the [units available] measure was blank. Works like a charm, as you can see below. Still have no clue why it replaces all other dates with 0 without this condition. But not going to rack my brain anymore... 😄 Thanks again! Just chatting with you seems to lead me to a solution everytime!

New measure (guess I will test if the first condition is redundant now, since it already passes through [units available]):

Vacancy = 

Var OPEN_Date = MIN('Lista Tipologie delle Risorse'[InizioStagione])
Var Close_Date = MAX('Lista Tipologie delle Risorse'[FineStagione])
Var d = MAX('Calendar'[Date])

VAR vacancy_ = [Units Available] - [Occupancy]

RETURN IF(d < OPEN_Date || d > Close_Date, BLANK(), IF(ISBLANK([Units Available]), BLANK(),vacancy_))

Screenshot of it working:

[MJEnnis]

@Greg_Deckler you are right that it is having some effect on the data. Good eye! Essentially what it is doing is it is expanding the filter context to all possible weeks in the calendar table, even ones for which there is no data for the selection. But then it replaces all values that are not within the selected holiday week with a 0, including the blanks. Hmm... so it is filtering the results in a sense, just not the visual. The measures are all set to return blanks if there is no data, so I guess I have to adjust the measures to account for this new filter context?

[MJEnnis]

@Greg_Deckler This is the measure that populates the visual in the image. It considers the season opening and closing dates for the product in question. It should return blanks for all dates outside the "season", and the visual normally automatically collapses to exclude those date ranges. That is the way it works with the other slicers on the page. But this new one replaces those blanks with zeros and includes them. It also replaces with zeros all values within the "season" but outside the selected holiday week. Maybe I could try to create a new measure that determines if the [date] is included in the current selection of holiday weeks or not. Then use that new measure to add a condition to the measure below? On the right track?

Vacancy = 

Var OPEN_Date = MIN('Lista Tipologie delle Risorse'[InizioStagione])
Var Close_Date = MAX('Lista Tipologie delle Risorse'[FineStagione])
Var d = MAX('Calendar'[Date])

VAR vacancy_ = [Units Available] - [Occupancy]

RETURN IF(d < OPEN_Date || d > Close_Date, BLANK(), vacancy_)

[Greg_Deckler]

@MJEnnis Hmm. Something is not right then. What I would do is that since you are using VAR's (good job), I would try returning OPEN_Date and then Close_Date, and d. No IF statement or anything, just return each of those one at a time to see what their values are within the context of the table visual when you make a holiday selection in the slicer. I'm guessing that something is potentially going haywire where it is always returning vacancy_ and not BLANK because of some reason.

[MJEnnis]

Note that the year range in the calendar table is currently 2014-2025.