Forum Discussion
How to make a custom date table for filtering using dates from different tables?
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.
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.
4 Replies
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.
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.
