Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! 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
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |