How to make a custom date table for filtering using dates from different tables?

[JaySharma]

I have 3 tables; let's call them table A B and C. I have dates in those tables: DateA, DateB and DateC.

I need to use these as filters using two slicers. one as a dropdown to choose between the names DateA, DateB, or DateC, and the second slicer, the beteween date slicer, should populate the dates from the correct table. 

My idea was to create a fourth table with a DateType column with the remaining 3 dates. Something like this, but I'm unable to achieve it using DAX for a table. 

This is something I need to achieve: 

DateType	Date1	Date2	Date3
Date1	Date1 Values	NULL	NULL
Date1	Date1 Values	NULL	NULL
Date1	Date1 Values	NULL	NULL
Date1	Date1 Values	NULL	NULL
Date2	NULL	Date2 Values	NULL
Date2	NULL	Date2 Values	NULL
Date2	NULL	Date2 Values	NULL
Date3	NULL	NULL	Date3 Values
Date3	NULL	NULL	Date3 Values

The idea after that is to link up these Date colums to their respective original tables.

[Ashish_Mathur]

Hi,

First and foemost, you should right click on the first column in Power Query and select "Unpivot Other Columns".  Then create a Calendar Table.

[AllisonKennedy]

@JaySharma  do you have a DimDate table? It might make things a little simpler for what you're trying to do - you could then create a measure that shows the max or selectedvalue of the date and show all three at once.

If you're wanting a slicer for users to be able to select only A, B or C, then using calculation groups and / or Field Parameters may help. 

[JaySharma]

@AllisonKennedy I do have the DimDate table, but I doubt if that would serve any purpose. I need 2 slicers, 1 to select the date type (Date of purchase/date of delivery/date of return) something like that and then the second between-date slicer to filter the data based on the selected date type. 

I have a star schema and I brought in all the dates to the central fact table to make stuff easier as the dates were coming from different tables. I tried creating a new table with dax wigh columns (Date type | DateType1 | DateType2 | DateType3) but I can't connect these three columns to three source columns in the fact table.

[Irwan · Accepted solution]

hello @JaySharma 

please check if this accomodate your need.

1. create new table (fourth table as you mentioned) using UNION to combine all date value

DateType =
var _Union = UNION('Date A','Date B','Date C')
Return
SELECTCOLUMNS(_Union,"Date",[Date A])

2. create calculated column for Date1

Date1 = MAXX(FILTER('Date A','Date A'[Date A]='DateType'[Date]),'Date A'[Date A])

3. create calculated column for Date2

Date2 = MAXX(FILTER('Date B','Date B'[Date B]='DateType'[Date]),'Date B'[Date B])

4. create calculated column for Date3 

Date3 = MAXX(FILTER('Date C','Date C'[Date C]='DateType'[Date]),'Date C'[Date C])

Result: 

Hope this will help you.

Thank you.