Forum Discussion
Cross join tables
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
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.
3 Replies
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
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:
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.
