Forum Discussion

JaySharma's avatar
JaySharma
Frequent Visitor
2 years ago
Solved

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 name...
Need Help
  • Irwan's avatar
    Irwan
    2 years ago

    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.

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
JaySharma
Frequent Visitor
2 years ago

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's avatar
    Irwan
    Icon for Super User rankSuper User
    2 years ago

    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.