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:
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:
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!
Go to 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...
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
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.
Check out new user group experience and if you are a leader please create your group!
Check out how to claim yours today!
Test your skills now with the Cloud Skills Challenge.