cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
herbemischung
Resolver I
Resolver I

two tables with dates one filter

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!

 

 

2 ACCEPTED SOLUTIONS
ImkeF
Super User II
Super User II

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...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Anonymous
Not applicable

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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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.

View solution in original post

ImkeF
Super User II
Super User II

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...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors