How to set up 3 Slicers for 3 types of date (columns)?

[Anonymous]

I have a fact table with 3 date columns, e.g.

I want to create 3 slicers for each of the 3 date columns for filtering.  For example:

• when I check 2018 in the slicer for order-date and 'select all' for other 2 date slicers, I can see the sum(sales) for order-date-year=2018;
• when I check 2018 in the slicer for ship-date and 'select all' for other 2 date slicers, I can see the sum(sales) for ship-date-year=2018;
• when I check 2018 in the slicer for delivery-date and 'select all' for other 2 date slicers, I can see the sum(sales) for delivery-date-year=2018.

Should I create 3 date tables for joining with the 3 different date columns from the Fact table? Or is there an easy way to achieve the purpose?

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

Three copies of the data table will be the way to go I think.  If you have a date table already you can make a copy of it in DAX simply by going to Modeling > New Table and entering the code something like:

Ship Date Table = DateTable

After that you can rename the columns in the copy of the table so instead of 'Year' is it 'Ship Year' but you don't need to.

[v-lili6-msft · Accepted solution]

hi @Anonymous 

There are two ways for you refer to:

1. Create 3 date tables for joining with the 3 different date columns from the Fact table as you said, you could copy the date table twice as jdbuchanan71 said to get three date tables.

2. Unpivot the fact table then use only one date table, selected order-date column, ship-date column and delivery-date column, then unpivot these columns and use [Attribute] as a slicer and [Value] column to connect with date table.

https://radacad.com/pivot-and-unpivot-with-power-bi

Regards,

Lin

[Anonymous]

Hi, 

From what i understand, if you don't have the need to create a date dimension, you could use the 'date hierarchy' directly to filter for years. 

[Anonymous]

I can't directly use the 3 dates from the fact table for the 3 slicers; otherwise, an error is gonna pop up like: ...expects a contiguous selection when the date column is not unique...

[Anonymous]

Got you! Thank you 🙂

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

Three copies of the data table will be the way to go I think.  If you have a date table already you can make a copy of it in DAX simply by going to Modeling > New Table and entering the code something like:

Ship Date Table = DateTable

After that you can rename the columns in the copy of the table so instead of 'Year' is it 'Ship Year' but you don't need to.