DAX DropDown slicer custom, multiple periods DateFrom DateTo

[Anonymous]

Hello,

I have a basic DAX problem.

I have a fact table and a date dimension, in Azure Analysis Services. The date dimension filters the fact table with a DateKey (INT)
I created a "Slicer" table from scratch in SQL and imported it into AAS, hoping to implement a custom slicer with periods

SlicerTable : 

<Label>, <DateFrom>, <DateTo> 

Week-1, 28/03/2022, 03/04/2022
Week-2, 21/03/2022, 27/03/2022
Week-3, 14/03/2022, 20/03/2022
etc.

I created a slicer dropdown list in PowerBI on the Slicer[Label] field. The Slicer table has no relation in the model with the other tables
The slicer allows multiple-selections, the goal is to be able to select disjoint periods example: Week-1 and Week-3 but without displaying the days of Week-2

I created a DAX formula to filter my measure, this new measure is displayed in a visual, which is broken down by day (DateDimension[Date])

NewMeasure = 

VAR MX = MAX('Slicer'[DateTo])
VAR MN = MIN('Slicer'[DateFrom])

RETURN
    CALCULATE (
		[MeasureToFilter], 
		FILTER('DateDimension',  
			MN <= [Date] 
			&& 
			[Date] <= MX
		)
	)

It works well but it does not allow to select disjoint periods. If Week-1 and Week-3 are selected, the MN and MX limits values are 14/03/2022 and 03/04/2022 respectively.

Il searched on the web, i think there is a need to iterate on the Slicer table, to select the two periods independantly.

Can you help me ?

Thank you in advance,
Romain

[OwenAuger · Accepted solution]

Hi @Anonymous 

Yes, you will need to iterate over the 'Slicer' table, and the function I would suggest is GENERATE.

Try this:

NewMeasure =
CALCULATE (
    [MeasureToFilter],
    GENERATE (
        'Slicer',
        DATESBETWEEN (
            'DateDimension'[Date],
            'Slicer'[DateFrom],
            'Slicer'[DateTo]
        )
    ),
    -- This assumes you want to override any existing 'DateDimension' filters.
    -- This is not needed if 'DateDimension' is marked as a date table in AAS.
    REMOVEFILTERS ( 'DateDimension' )
)

Note:

• GENERATE iterates over 'Slicer' and produces the appropriate date range for each row. These date ranges are then effectively unioned, and treated as a combined filter.
• I wasn't sure if your AAS dataset has 'DateDimension' marked as a date table. If so, the final REMOVEFILTERS ( 'DateDimension' ) would not be needed.
• If you want to intersect the new date filter with existing filters, you could wrap GENERATE in KEEPFILTERS.

Regards,

Owen

[Anonymous]

Hello,
Thanks for you reply, it worked with the following code.
You were right, i had to wrap the GENERATE in KEEPFILTERS, because of the existing filter context :

NewMeasure = 
	CALCULATE ( 
		[MeasureToFilter], 
		KEEPFILTERS(
            GENERATE ( 
			    'Slicer', 		
			    DATESBETWEEN ( 'DateDimension'[Date], 'Slicer'[DateFrom], 'Slicer'[DateTo] ) 
		    )   
        )	
    )

Thank you also for the explaination
I put your answer as solution

Regards,
Romain