Aligning dates

[cmcreynolds]

I have multiple dates I'm pulling via PQ and I also have a calendar table. If I want to use a timeline to filter ALL (maybe 5?) my dates, do I create relationships between the calendar table and every one of the 5 fields I'm pulling?

 

[ImkeF]

Not too sure if I got your question right:
Shall this timeline work/filter during the load of the data or does your question mean how you join the tables in the data model in order to do all sorts of interactive slicing on the reports then?

 

[scottsen]

You can not create multiple active relationships between the same two tables. Typical example is a Calendar table and an Orders table that has Orders[OrderDate] and Orders[ShipDate]. Only 1 of those can be Active. Measures against the non-active column would need to use the USERELATIONSHIP() function to activate it.

 

[cmcreynolds]

Imke - I was torn between those two - filtering by dates while loading (PQ) vs at the data model and tables. This is going to be a monthly report, so either would work, I suppose. You have a preference?

 

[cmcreynolds]

@Scottsen - I forgot about USERELATIONSHIP()...but the relationship still has to be there, right? (the dotted lines in the data model)

 

[ImkeF]

What a question
But I cannot recommend anything to you, it completely depends on what other aspects there are in your solution.

One important thing to consider here: Power Query can only feed Power Pivot and not SSAS tabular directly. So if you intend to transform to tabular - this would not work.

Advantage is that your DAX life might be easier with this approach.

Just have a look at my latest blogpost

 

[cmcreynolds]

@Imke

oooh...query folding. I've often wondered about that. Thanks.