3 Tables, 1 date slicer

[Anonymous]

I feel like this is probably an easy question and i'm just not seeing the obvious.  I've got 3 tables, Site List, Materials Processed, Hours.  Site List has a 1 to Many relationship with both tables.

I've created a report with a Timeline Slicer that uses the dates from the Materials Processed Data table.  I've created some measures that get a SumX of Materials Processed and a SumX of Hours data.  I then have a last measure that is a Materials Processed divided by the hours.  These measures are within the Site Table.

The problem I am facing is that while date slicer is correctly limiting the Materials Processed to whichever date range is selected, its not effecting the Hours data.  I was hoping that the same date limitation would be applied to the Hours table, but i've not been able to make it do it.  Both tables have a date field both with the same name and of type 'Date'.

[GilbertQ · Accepted solution]

Hi @Anonymous,

To solve your current question, what you need to have is a Date table, and then link your 3 tables Site List, Materials Processed, Hours to the Date table via a relationship.

If you need to understand what a Date table does you can read this blog post: Do You Need a Date Dimension?

Next if you now want to go ahead and create a date table, here is my blog post explaining how to easily do this: Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query

And once you hvae created the relationships, you can then create your date slicer from the Date table. Which will then filter all the tables included in the relationships.

[Anonymous]

Thanks! I knew it would be something easy that i had overlooked.

[GilbertQ]

Hi @Anonymous,

Glad to have helped.