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.
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.
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])
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.