Multiple Filter with a data table

[Anonymous]

Hello guys,

I just started using PBI a week ago and i'm kind of stuck now.

I will simplify my problem to make it easier to understand.

I have 4 tables:

1- Sales table, with all the sales for each city and product

2-Product table, that link the product to its group

3-Territory table, that links the city to its region

4-Sellers table, that tells you on wich territory and what group of product each seller is selling.

Before inserting table 4 all was good.

But with this table in the model i cannot link it to my 2 table without having an ambiguous relationship.

My goal is to choose one of my seller and see only his territory and products.

Is there a solution that i didn't think of?

The only solution I have would be to put the Territory and Product Group in the table 1, but I think it won't be very nice. I don't really want to get my table bigger.

Thank you for your help,

Greg

[JarroVGIT]

You can link it to table 2 just fine, as long as Product Group in table 4 has unique values, like in your example (e.g. as long as one product group is only sold by one seller). However, you can't link it to table 3, because none of the columns have unique values and you will create a many-to-many relationship. 

In your case, it is important what you would like to get out of the information of these tables. What are you trying to achieve?

[Anonymous]

Thank you for your answer.

Actually, in my real life situation one product group can be sold by multiple sellers (I can even have two sellers that sell the exact same products on the same area, in that case if we choose one seller or the other, the results on PBI would be the same).

In the sales table, I do not have the name of the seller (it is indirect sales), I just have the localisasion and the product sold.

I am trying to get the sales of each sellers according to its Products and Territory.

So if we focus on Jean, we will keep only the line 1 and 3 on my table 1: So we have 10+12 = 24 units

Just for info, i added a 3rd seller that does 1 group product more than Jean (Group F).

If I select Lucas, I will also keep the 5th line on my table 1, so I will have 10+12+21 units

[Anonymous · Accepted solution]

Hola,

Finally I used this link to help me solve the solution, I don't really like it but I cannot find something else:

https://www.sqlbi.com/articles/creating-a-slicer-that-filters-multiple-columns-in-power-bi/

I basically added to my table 4 the City level and Product level.

Then I created in Table 4 and Table 1 a Column that contains 'City & Product'.

Then I link directly Table 1 and 4 with this new column.

[Icey]

Hi @Anonymous ,

Glad to hear that. Please accept your reply as a solution so that people who may have the same question can get the solution directly.

Best Regards,

Icey