Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |