Exclude bank holidays selected from slicer, from a separate date slicer

[PaulHallam]

Hi All,
I have a 'Date' table with all dates 2024.
I have a 'BankHolidayDates' table with all bank holiday dates 2024

I want a date slicer on report page that includes all dates from the 'Date' table
I want a bank holidays date slicer on report page that includes all dates from the 'BankHolidayDates' table

When i select an item in the 'BankHolidaysDates' slicer i want it removed from the Date slicer.

So effectively the 'BankHolidaysDates' slicers title will be "Exclude Bank Holidays" with a select all option to remove all

Any one got any ideas?

Thanks

[v-junyant-msft · Accepted solution]

Hi @PaulHallam ,

Please make sure there is no relationship between the two date tables:

Please use this DAX to create a measure:

Filter = 
IF(
    ISFILTERED(BankHolidaysTable[Date]),
    IF(
        MAX('Calendar'[Date]) IN VALUES(BankHolidaysTable[Date]),
        0,
        1
    ),
    1
)

Set it up as shown below:

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulHallam]

Thanks Dino. This will achieve what i need, but doesnt work with the select all filter

[v-junyant-msft]

Hi @PaulHallam ,

This may be a limitation of the "Select all" option in the slicer itself, because the "Select all" option itself does not exist in your data column. When you select "Select all", although the slicer will work, DAX cannot recognize which data you have selected.

Best Regards,
Dino Tao

[MattiaFratello]

Hi @PaulHallam,

Please try the following:

In your 'BankHolidayDates' table create a column which is always equal to 1.
In your 'Date' table create a calculated column as follow

IsBankHoliday = 
IF(
    CONTAINS(Holidays, Holidays[Date], Dates[Date]),
    0, 
    1
)

Create a relationship btw the two tables as follow:

Let me know if this helped,
All the best

[PaulHallam]

Hi Mattia, i had already tried soemthing similar and this doesnt seem to work. Linking the 'is holiday' columns would be a many to many relationship. 
I need to Slice bank holidays out of the main dates slicer, what you have shown is not this case

[Greg_Deckler]

@PaulHallam Sounds like you need a complex selector. https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M5...

Basically, check if you have a single value for BankHolidaysDates. Check if the current date is the selected date and if so return 0, otherwise 1. Filter out the 0's in your Filter pane for that visual using the measure.

[PaulHallam]

Hi Greg,

Well complex was the word......! I cant seem to get it to work as i can't get the use case/fields to match my case.
Below is the use case (image)

These are the tables if anyone wants to have a go, it would very much be appreciated.

BankHolidaysTable

01 January 2024	New Year’s Day
29 March 2024	Good Friday
01 April 2024	Easter Monday
06 May 2024	Early May bank holiday
27 May 2024	Spring bank holiday
26 August 2024	Summer bank holiday
25 December 2024	Christmas Day
26 December 2025	Boxing Day

DatesTable 

= List.Dates( #date(2024,01,01), 366, #duration(1,0,0,0))

Any help much appreciated.

Paul