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.
I have a Dim_Week table and Fact_OrderLines table. Dim_Week table has Start Date and End Date columns, OrderLines table has Order Date column, How can I establish relationship here? What is the importance of Start Date and End Date?
Download Pbix file from below link
Note: Ignore Date Table which is created by me. Consider Dim_Week table and how can we connect it to Orders table.
hi @anilpoda , you can not create a relationship on two columns from the same table. So you have two options. you build some funky dax to filter between start and end, or.... you expand the Dim_Week table. what you need to generate in the end has to look something like this:
Date | Week| Start | End
02.01.2023 | WK1 | 02.01.2023 | 08.01.2023
03.01.2023 | WK1 | 02.01.2023 | 08.01.2023
04.01.2023 | WK1 | 02.01.2023 | 08.01.2023
05.01.2023 | WK1 | 02.01.2023 | 08.01.2023
06.01.2023 | WK1 | 02.01.2023 | 08.01.2023
07.01.2023 | WK1 | 02.01.2023 | 08.01.2023
08.01.2023 | WK1 | 02.01.2023 | 08.01.2023
09.01.2023 | WK2 | 09.01.2023 | 15.01.2023
10.01.2023 | WK2 | 09.01.2023 | 15.01.2023
etc.
you also have to make a decision how to handle 01.01.2023, as still sits in WK52 from 2022 for some calendars.
Hope this helps. I won't tell you how to explode the list straight up, just a tip: Have a look at the LIST funktion in Power Query and think about ways to create a "Cross -Join".
Hi,
Create a key Key FY_Wk like in Dim_Week for Date Table and Order Lines, based on Order Date. Then simply Date-Week Table-Order Sales Table.
Hi Olgad,
I was told by the company there is no need to create a seperate date table. You have to use Dim_Week table. Question here is, there are 2 date columns in Dim_Week table Start Date and End Date. How do I proceed in this situation? How can I establish relation between Dim_Week table and Fact_OrderLines.
Note: There is Fact_OrderHeader with all the order dates. Kindly download the file and propose a solution. I'm stuck with this for over a week.
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 | |
105 | |
78 | |
67 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
83 | |
70 |