Forum Discussion
Date table not filtering multiple dates in fact tables
Hello,
In my model, I have three fact tables: SegmentData, TimeReporting and CustomerCharges. Each of these tables have various dates. I have a separate Date table with a relationship (omnidirectional, 1 to many) from the Date field to a particular date field in each of the fact tables. All date fields being used are set to the same date/time type. On my report, I have a date slicer using the Date field from the Date table. I have graphs pulling data from two of the fact tables - SegmentData and TimeReporting - and I want each of these to update based on the date selected in the slicer. However, I am finding the data from one fact table (TimeReporting) is updated based on the dates selected, but SegmentData is not filtering correctly. For one customer selected - who has fact data for this year from May thru November - if I move the start date in the slicer it shows no data for the customer at all, even though the date is prior to the dates of their transactions in the fact table.
I've uploaded the pbix here: https://1drv.ms/u/s!AjomjdFc4LMegbs24YaaznbTks51tA
I appreciate any help anyone can give!
Thanks,
Stacey
Hey Stacey,
I guess you can easily fix the issue, the following screenshot shows that the column LastSaved from the "SegmentData" column also has a time part, whereas the date from the date table does not ...
I used a simple measure to check the dates both from SegmentData and Date table. As soon as you select a value from the date table in a slicer, there will be no match. This is also flected by the Blank member in the year and month slicer. You can think of this in a way as "values" from the many side of a relationship that are not existing as a value in the column on the one side are propagated as Blank to the slicer.
I recommend that you just create an additional column in the DataTable either as an additional step in the query or by using this little DAX statement:
LastSavedDate = DATE( YEAR('SegmentData'[LastSaved]) ,MONTH('SegmentData'[LastSaved]) ,DAY('SegmentData'[LastSaved]) )Hopefully this is what you are looking for, at least provides you an idea
Regards,
Tom
4 Replies
- TomMartens
Super User
Hey Stacey,
can you describe which customer and which year? Are you meaning 2018, because of "... this year ..."?
Please describe the reason for the relationship between the tables "Customer Chargers" and "Time Reporting" and why this is a "many to many" relationship.
What are you expecting for "SegementData"?
Regards,
Tom
Hello TomMartens,
The many-to-many relationship was from a prior attempt and I had neglected to remove that relationship. For this example, I am selecting customer Manitoba Hydro. They have data in the Segment and Time fact tables for May 2018 thru November 2018. When I select dates in the slicer, I expect to see:
1) FROM TIMEREPORTING should update to the hours submitted during those dates (TimeReporting table)
2) FROM SEGMENTDATA graphs should update to break down the Treated/Not Treated/Other data entered during those dates (SegmentData table)
3) FROM SEGMENTDATA cards should update to refect the total segments and total length entered during those dates (SegmentData table)
Thank you,
Stacey
- TomMartens
Hey Stacey,
I guess you can easily fix the issue, the following screenshot shows that the column LastSaved from the "SegmentData" column also has a time part, whereas the date from the date table does not ...
I used a simple measure to check the dates both from SegmentData and Date table. As soon as you select a value from the date table in a slicer, there will be no match. This is also flected by the Blank member in the year and month slicer. You can think of this in a way as "values" from the many side of a relationship that are not existing as a value in the column on the one side are propagated as Blank to the slicer.
I recommend that you just create an additional column in the DataTable either as an additional step in the query or by using this little DAX statement:
LastSavedDate = DATE( YEAR('SegmentData'[LastSaved]) ,MONTH('SegmentData'[LastSaved]) ,DAY('SegmentData'[LastSaved]) )Hopefully this is what you are looking for, at least provides you an idea
Regards,
Tom