Best way to model many to many data problem

[walker4545]

Hello Power BI Community,

I'm hoping for assistance with the below problem. I have two lookup tables showing the Department to which each product and region pertains, and a regions table showing to which region a store pertains. 

Product Table

Region	Product	Department
Germany	Videogame	Multimedia
France	Videogame	Visual Media
Spain	Videogame	Games, Toys and Boys Clothing
Germany	DVD	Multimedia
France	DVD	Visual Media
France	Basketball	Clothing and Sport
Italy	Basketball	Sports
Germany	Lego	Clothing, Sport and Toys
France	Lego	Toys
Portugal	Lego	Games, Toys and Boys Clothing
Germany	Socks	Clothing, Sport and Toys
Italy	Socks	Clothes
Spain	Socks	Games, Toys and Boys Clothing

Region Table

Region	Store
Germany	Berlin
Germany	Munich
Portugal	Porto
Italy	Milan
Italy	Rome
Spain	Barcelona
Spain	Madrid
France	Paris
France	Marseille

Finally, there is a facts table showing sales that have been made, by  product, store location, Qty, cost and total sale (Qty*cost).

Sales Table

Qty	Store	Item	Price	Total
2	Berlin	DVD	€10.00	€20.00
1	Madrid	Videogame	€70.00	€70.00
1	Paris	Basketball	€30.00	€30.00
2	Paris	DVD	€10.00	€20.00
3	Berlin	Videogame	€70.00	€210.00
1	Munich	DVD	€10.00	€10.00
1	Porto	Lego	€15.00	€15.00
1	Milan	Basketball	€30.00	€30.00
1	Rome	Basketball	€30.00	€30.00
2	Barcelona	Socks	€5.00	€10.00
1	Madrid	Videogame	€70.00	€70.00

As part of the reporting requirement I need to determine sales by department, region and store (both independantly and in combinded tables). As you can see, joining the lookup tables with one another would create a many to many relationship of region to region. 

I would greatly appreciate if someone could assist on how this would best be modelled. Any suggestions whatsoever including DAX or Power Query solutions would also be appreciated.

Thanks in advance.

[v-henryk-mstf]

Hi @walker4545 ,

Refer to the following data model to see if it meets your requirements.

For more details, you can read related document.

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

If the problem is still not resolved, please point it out and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@walker4545 , Using Region Table( Merge In power query)  add Region to sales Table Create a combine key with Region and item

Create a key product table using region and product and then join these two tables using this new key