Forum Discussion

DeBIe's avatar
DeBIe
Post Partisan
3 years ago
Solved

How to filter by two columns?

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 ...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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.

Ahmedx's avatar
Ahmedx
Super User
3 years ago

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's avatar
    DeBIe
    Post Partisan
    3 years ago

    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?