Forum Discussion

common763's avatar
common763
Icon for Helper III rankHelper III
2 years ago
Solved

Multiple Date Calculations in Slicer from Row data

I have a dataset that has multiple different date calculations on the same row.  Date1,2,3,etc.  There are 12 separate date differential calculations between about 6-8 different date columns on the ...
Need help for Dax
Slicer
  • jgeddes's avatar
    jgeddes
    2 years ago

    Here is a DAX version of ahadkarimi 's answer. (The power query version is way easier for larger datasets.)

    categoryTable = 
var _t1 = 
SELECTCOLUMNS(
    surveyTable,
    "_survey", [Survey],
    "_category", "cat1",
    "_value", DATEDIFF([Date1], [Date2], DAY)
)
var _t2 = 
SELECTCOLUMNS(
    surveyTable,
    "_survey", [Survey],
    "_category2", "cat2",
    "_value", DATEDIFF([Date1], [Date3], DAY)
)
var _t3 = 
SELECTCOLUMNS(
    surveyTable,
    "_survey", [Survey],
    "_category3", "cat3",
    "_value", DATEDIFF([Date2], [Date4], DAY)
)
Return
UNION(_t1, _t2, _t3)



ahadkarimi's avatar
ahadkarimi
Icon for Solution Specialist rankSolution Specialist
2 years ago

Hi common763,

Create Calculated Columns for each date difference (e.g., Date1 - Date2, Date1 - Date3, etc.).
Unpivot the Calculated Columns in Power Query to create a long format table.
Use the unpivoted column names as your slicer/filter.
So you can select different date calculations in a slicer.

Did I answer your question?  If so, please mark my post as the solution!✔️
Your Kudos are much appreciated!  Proud to be a Responsive Resident!