Forum Discussion
Multiple date columns in Fact table
my fact table has 4 date columns like as below
i have created a caleder table and joined all 4, obviously 3 of them are inactive and only one is active.
my ask is to have only one date slicer to filter the table data for all these date columns, i know that it works if i use userelationship, but that is when i want to calculate something. but here is dont want any calculations, my fact table has many columns, it is a simple table visual with different columns, user want to use only one date slicer so the below table visual will be filtered to only that for all the different date columns, how can we do that? any thoughst?
amitchandak lbendlin Greg_Deckler Daniel29195 Ritaf1983 Idrissshatila Ahmedx AlexisOlson
ltsantos ybarsabal
6 Replies
one approach you can take is to unpivot your table so that you will have :
datetype with values { inceptiondate, resolutiondate , submitteddate a, dateoflastupdate )and the second column is the dates .
this way you will have one date column to link to your dimdate and the other column specifiyin the type of the date on the current row .
this can be done in power query under transform --> unpivot columns.
hope this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠Daniel29195 Hi, thanks for taking your time to respond, the problem is, i am using existing semantic model as my source in the report, i dont have any control as it is direct query and we cant ask for import mode due to sensitivity issues. looks like it is not possible?
you can maybe ask the backend team to unpivot the table for you , but keep in mind that the nb of rows of this table would be multiplied by 4 .
other than this, im not sure if you can achieve your requirement in another way .
plus, since each column consist of a different business logic, so it is not ideal to have one slicer that filters all of them, unless you can specify what type of date you are filtering on . ( thus you need to unpivot your table )
hope this makes sense.
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
I don't quite understand what you need
If you selected 9/5/2012 , then what result are you expecting?
and if you choose 2/7/2024 then what are you waiting forAhmedx basically if i sleect one date, say like 9/5/2012, my below table should be filtered for the same date for those 4 date columns, inception date, submitted date, etc..
johnbasha33 , In this better not to join the date table and try like
Measure =
var _max = maxx(allselected(Date), Date[Date])
return
calculate([Your Measure], filter(Table, Table[Inception Date] =_max && Table[ResolutionDate] =_max)))
you can use OR (||) , also add other dates as per need
