Forum Discussion
Modeling Relationship
i want to create a relationship between the stores and sale table. however, by doing so, it will create an ambiguity error. my main goal is to create a relationship between these 3 tables, so that when i want to find out the sale of each store, it is possible. at the same time, when i want to find out the sale of each channel type, it is possible. so far i can only get the sale by channel type, but cant seem to do it for the store table. help would be greatly appreciated 🙂
2 Replies
Hi Anonymous
Do you have keys in your dimension table for the store, and do you have a key in your sales table for the store the sale was made in?
Your output does not seam to be that hard, but this depneds on the make up of your tables and data.
Are you using this to model your schema? Understand star schema and the importance for Power BI - Power BI | Microsoft LearnThere are likely a number of ways to do what you are looking for.
My first suggestion would be try and delete the relationship between the Channel and Sales table. Then build a relationship between the Store and Sales table. In theory when you select a Channel it would then filter the Store table which would then filter the Sales table to get your result.
If that does not work, you leave your existing relationships and then you can build the relationship between Store and Sales and leave it inactive. In any measures you create that require the Store->Sales relationship you would need to use the USERELATIONSHIP() function.
Another approach would be to use the TREATAS() function to create a virtual relationship between the Store and Sales tables.
Hope this gets you pointed in the right direction.
