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 need help cross joining two tables.
I have one table that looks like this:
Table "Openings hours"
Store | Weekday | Hours open |
Store 1 | Mo | 8 |
Store 1 | Tu | 7 |
Store 1 | We | 5 |
Store 1 | Th | 10 |
Store 2 | Mo | 4 |
Store 2 | Tu | 3 |
Store 2 | We | 2 |
Store 2 | Th | 1 |
Then I have a Dim Date table:
Date | Weekday |
feb 1 2021 | Mo |
feb 2 2021 | Tu |
feb 3 2021 | We |
feb 4 2021 | Th |
and so on...
I would like to create a new table that gives me the opening hours per date and store:
Store | Date | Hours open |
Store 1 | feb 1 2021 | 8 |
Store 1 | feb 2 2021 | 7 |
Store 1 | feb 3 2021 | 5 |
Store 1 | feb 4 2021 | 10 |
Store 2 | feb 1 2021 | 4 |
Store 2 | feb 2 2021 | 3 |
Store 2 | feb 3 2021 | 2 |
Store 2 | feb 4 2021 | 1 |
and so on....
I need help with a smart solution!
Cheers!
Maria
Solved! Go to Solution.
In the Query Editor, you can merge Opening Hours table onto the Dim Date table joining on Weekday and then expand the Store and Hours open columns.
@Anonymous ,
Step 1: Join the two tables based on the Weekdays.
Step 2: Then drag the Stores, date, and hours open from the respective tables on Table visual.
@Anonymous
If you want to do this in a visual in the report, you can use:
1)
Sum of hours = SUM('Opening Hours'[Hours open])
2)
Hours by date =
CALCULATE (
[Sum of hours],
TREATAS ( VALUES ( 'Date Table'[Weekday] ), 'Opening Hours'[Weekday] )
)
and create the visual using the Date field from the Date Table and the Store from the Opening Hours Table:
Proud to be a Super User!
Paul on Linkedin.
In the Query Editor, you can merge Opening Hours table onto the Dim Date table joining on Weekday and then expand the Store and Hours open columns.
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 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |