How to filter by two columns?

[DeBIe]

Hey all,

I'm not sure how to built the following customer requirement.

In my report I have companies and company codes. Every month our customer will send us an Excel-file with company codes and two extra columns with sales persons. They would like to have one filter for the sales guys that are responsible for the company. I will show an example below:

Excel file deliverd by customer: 

CompanyID	CompanyName	SalesPerson1	SalesPerson2
1A	Wallmart	Bob	Helen
1B	Billy's	Helen	Bob
1C	Mindy's	John	Bob
1D	BurgerKing	Carl	Cindy

PowerBI Dataset

CompanyID	CompanyName
1A	Wallmart
1B	Billy's
1C	Mindy's
1D	BurgerKing

The end result scenario should be the following. There has to be one filter called "SalesPerson". If the end-user should select "Bob" in this filter, then the following data must show:

CompanyID	CompanyName
1A	Wallmart
1B	Billy's
1C	Mindy's

Thank you for your time. 

[Ahmedx]

look at this option
https://dropmefiles.com/ObgBn

[DeBIe]

Hi @Ahmedx 

Thank you for your time and suggested solution. I've implemented @johnt75 solution and that works fine already for me.

[johnt75 · Accepted solution]

You need to tweak the model. Firstly, get all the salespeople into one column by opening power query and selecting the company ID and company name columns from the excel file then unpivot other columns. rename the value column to salesperson and delete the attribute column as you don't need it.

You then need to create a table which contains the distinct salespeople. You can either do this in power query or in DAX.

You can now use the table from excel as a bridge between the companies and the salespeople, and link it your fact table.

See the attached PBIX for a sample.

You might also find https://www.sqlbi.com/articles/different-options-to-model-many-to-many-relationships-in-power-bi-and... from SQLBI useful.

[DeBIe]

Hi @johnt75 

Thank you for your time and suggest solution.

I've tried your solution. However, I need it to work without the measure that you've added. I need to be able to select a sales person and that should then also filter down the companies. This does not work when I remove the measure. 

[johnt75]

I can think of a couple of options. You could either use the Company Name column from the bridge table, or you could make the relationship between the bridge table and the PowerBI table bidirectional.

If you are only interested in the company name then I would probably choose the first option, but if there are other attributes about a company that you need filter then you will need to use the bidirectional relationship.

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://dropmefiles.com/8v7fK
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[DeBIe]

Hey @Ahmedx  thank you for your suggested solution.

I need to be able to apply the filter to several pages, instead of one or multiple visuals. The difficulty I see here is that I need to add the measure on visual level?