Cross join tables

[Anonymous]

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

[Tahreem24]

@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. 

[PaulDBrown]

@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:

[AlexisOlson · Accepted 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.