two tables with dates one filter

[herbemischung]

Hi, I tried to get along with the forum but I failed, maybe you can help me:

I have a Table with actions which took place on spereate dates in specific regions:

Actiontable:

Action 1 - 01/01/2015 - region 1 

Action 2 - 02/01/2015 - region 2... and so on

And there is also a Table that defines these regions. Some attributes of the regions can change over time, so there is the following structure:

Regiontable:

Region 1 - Size x - ... - 2015

Region 2 - Size y - ... - 2015

....

Region 1 - Size x+1 - ... - 2016

Region 2 - Size y+n - ... - 2016

...

I can't just create a link between the dates of the actiontable and the regiontable because i have an n:n connection.

I tried to create a separate Table with the Calendar function to merge those 2 Datecolumns. 

But the sizes of my regions keep getting summed up. 

All I need is to have one Date-Filter that applies to all tables.

Thanks in advance!

[ImkeF · Accepted solution]

You can either use a bucketing-approach: https://blogs.msdn.microsoft.com/analysisservices/2014/06/05/bucketing-values-in-dax/

or create a calculated table in DAX which expands your lookup-table with one row per date. Then you have a 1:1-relation with your Actiontable (Order-table in the example) and a 1:n to your date-table: http://www.thebiccountant.com/2016/12/01/analyzing-events-with-a-duration-in-dax-further-simplificat...

[Anonymous · Accepted solution]

You can create a new table with this formula

Table = DISTINCT( UNION( DISTINCT(TableA[Date]) ; DISTINCT(TableB[Date]) ))

And then, create the relashionship with your two tables.

[ImkeF · Accepted solution]

You can either use a bucketing-approach: https://blogs.msdn.microsoft.com/analysisservices/2014/06/05/bucketing-values-in-dax/

or create a calculated table in DAX which expands your lookup-table with one row per date. Then you have a 1:1-relation with your Actiontable (Order-table in the example) and a 1:n to your date-table: http://www.thebiccountant.com/2016/12/01/analyzing-events-with-a-duration-in-dax-further-simplificat...