Forum Discussion
How to filter by two columns?
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-tabular/ from SQLBI useful.
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-tabular/ from SQLBI useful.
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.
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.