Forum Discussion
How to make a custom date table for filtering using dates from different tables?
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 Date2Date2 = MAXX(FILTER('Date B','Date B'[Date B]='DateType'[Date]),'Date B'[Date B])
4. create calculated column for Date3Date3 = MAXX(FILTER('Date C','Date C'[Date C]='DateType'[Date]),'Date C'[Date C])
Result:Hope this will help you.
Thank you.
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.
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])
Date2 = MAXX(FILTER('Date B','Date B'[Date B]='DateType'[Date]),'Date B'[Date B])
Date3 = MAXX(FILTER('Date C','Date C'[Date C]='DateType'[Date]),'Date C'[Date C])
Hope this will help you.
Thank you.