How to filter a table using two or more date slicers

[Peachyco]

I have a table of Purchase Orders, and my users need to filter it using two or more dates:

• PO Date: All records will have this since this is automatically generated at the time of PO creation.
• SIL Date: Not all records will have this entered. If the Supplier has not provided this date yet, then this value will be left null/blank for that particular Purchase Order.

With the above in mind, I have these slicers planned (screenshot below):

• PO Date (Between): The user selects the PO Date range, and the table is filtered accordingly. This slicer is working perfectly.
• SIL From Date (After): The user selects a month here, and the table is filtered for those PO's with SIL Dates that come on or after the selected month. The user should have the option to clear the selection here, in which case there will be no lower limit to the SIL Dates in the table. However, I can't make this slicer work as intended.
• SIL To Date (Before): The user selects a month here, and the table is filtered for those PO's with SIL Dates that come on or before the selected month. The user should have the option to clear the selection here, in which case there will be no upper limit to the SIL Dates in the table. However, I can't make this slicer work as intended.

Some extra notes:

• The dates in the table are all proper dates. I'm only using the "MMM-YY" format in the SIL From/To Date slicers because using full dates in a dropdown slicer makes for a very long list.
• If the user makes any selection in the SIL From/To Date slicers, then records with a null SIL Date should not be included in the report table.
• But if the user clears both the SIL From/To Date slicers, then records with a null SIL Date should be included in the report table.
• I have tried using SELECTEDVALUE to catch the selected SIL dates in a measure, but it's not working. To test, I tried to simply return the selected value, but it's coming as blank.
• I have tried an SIL Date slicer that uses the Between configuration (just like the PO Date slicer), but this causes records with no SIL Date to be removed from the report table. I need the user to have the option to include or remove such records.

To give you an idea of how the relevant data points appear in the report table, below is a partial screenshot (please don't mind the duplication as this report table actually handles PO lines, so the same PO can include different product/service lines):

Please advise on how we can design this report to meet our requirements. If you have a means of doing things differently from what I had in mind, I will also welcome it as I am willing to learn new approaches. Thanks a lot for any help that you can provide. 😊

[OwenAuger · Accepted solution]

Hi @Peachyco 

I have attached a sample PBIX with one way of approaching this.

I would recommend a modelling approach, by adding some additional tables to the model to handle the SIL From/To Date filtering logic. This avoids writing any DAX specifically to solve this problem (which might need to be repeated in multiple measures).

Model description

Here is the data model, with the additional tables in the red box.

• SIL MonthYear contains distinct SIL MonthYear values from SIL Date.
• SIL Range contains every "valid" combination of SIL From Date and SIL To Date (i.e. where SIL From Date ≤ SIL To Date). The rows are then expanded with SIL MonthYear containing every every MonthYear in the range. This table also has a single row with all values = BLANK. (Sample below).
• The reason for the blank row is so that when no selection is made on the SIL From/To slicers, blank will be included in the selection, and therefore blank SIL Date values in Purchase Orders will be included as a result of the filter. However, if a selection is made on either slicer, blanks will be excluded.
• When SIL From Date and SIL To Date selections are made, the SIL MonthYear values are then visible, and the appropriate SIL Date values in Purchase Orders will be visible by virtue of the relationships from SIL Range > SIL MonthYear > Sil Date.

SIL Range table

Report

• The report can then be set up with slicers on 'SIL Range'[SIL From Date] and 'SIL Range'[SIL To Date]. 
• Blanks can be hidden with a visual-level filter on each slicer.
• Even though blanks are hidden, they are still included in the selection when the slicers are in an unselected state (showing "All").
• Once a selection is made on either SIL From Date or SIL To Date
  1. Blank SIL Dates are automatically excluded
  2. Only the valid dates are shown on the other slicer.

Alternative model setup:

• Another method of setting up the model (which I think may be preferable actually) is to use two separate tables SIL From Date and SIL To Date, rather than SIL Range.
• See the 2nd page of the attached PBIX.
• This two tables SIL From Date and SIL To Date are naturally much smaller than SIL Range (which is effectively the crossjoin of these two tables).
• Using this method, visual-level filters can be placed on the two slicers to enforce valid combinations of From/To Date.

Notes:

• The SIL Range table has a row count of
  (1/6) * N * ( 2 + 3*N + N^2 ) + 1
  where N is the number of months.
  • For example for 12 years = 144 months, row count = 508,081.
  • Since each column's cardinality is just N + 1, the table should be stored quite efficiently.
• In the alternative model setup, the rows counts of each of SIL To Date and SIL From Date are
  (1/2) * N * ( N + 1 ) + 1.
  • For example for 12 years = 144 months, row count = 10,441
• If you wanted to enforce single selection on the slicers, this might require an approach like this.
• While I prefer the modelling approach, a DAX approach is also possible. I expect it would partly involve checking whether a filter has been applied on the From/To columns using ISFILTERED.

Regards,

Owen