Tables with multiple date fields

[mceldon]

Hi,

I have a single 'Incidents' table that has multiple date fields e.g. "CreatedOn" and "ClosedOn".

I've already hooked these fields up as foreign keys to a sinlge Date table which happily drives the behaviour I need for "CreatedOn" - thing is I don't understand how to do the same for the other date fields as I'm not sure how I allow for the differentiation - how can this be accomplished ?

Here's a snip for refernce;

[Anonymous]

Hello, i hope it's not too late. i think i know what you are looking for and where to find it.

You need to relate multiple dates from your fact table to a calendar table. All you need to do is use the USERELATIONSHIP DAX function.

Here is how:

https://www.youtube.com/watch?v=sONvctPlplY

and also you could use

https://msdn.microsoft.com/en-us/query-bi/dax/userelationship-function-dax

Hope it helps.

[v-qiuyu-msft]

Hi @mceldon,

What do you mean about "I don't understand how to do the same for the other date fields as I'm not sure how I allow for the differentiation"? It would be better if you could provide more information, so that we can understand the requirement clearly.

In your scenario,  as you mentioned 'Incidents' table contains many date fields and you have built relationship between 'Incidents' table and Date table based on CreatedOn" and "ClosedOn" fields, other date fields in 'Incidents' table can also interact with Date table. Please take a look at this article: Create and manage relationships in Power BI Desktop .

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[asocorro · Accepted solution]

I am not exactly sure what it is you are trying to accomplish, but you will need to consider into your solution the limitation of one active relationship between two tables.  Check out this post:

https://kzhendev.wordpress.com/2014/02/12/role-playing-dimensions-dax-vs-mdx-part-1/

[mceldon]

It was the limitation you mention that was causing an issue and i have overlooked. I reserched more the date table generation and now understand that date tables are generated for each date field auotmatically with an option to create my own using the AutoCalendar function. 

Thanks,

SJM

[ankitpatira]

@mceldon can you ellaborate more on your issue ? what you're trying to do for the differentiation ?