Display related data when filtered in Power BI

Question

Hello, here is my dataset :

What I would like to have is a filter on Campagne which shows all the other Campagne if the Contract number is the same. I explain myself. If I click on Campagne 3 in my filter, I want to see 1, 2, 3 and the attribution (the attribution by Campagne, not a Sum). Here is the expected result :

For now, the only solution that I have is to use a "temporary" table. But it's not optimal because I have to duplicate the data.

Any idea ?

Thanks a lot.

Answer 1

You are going to have to create an extra table or two to get the slicer to work how you want. There's no way around it, but you only need to duplicate part of the data. Here's what I would suggest.

---

Create a new Slicer table by summarizing the Campagne and Contrat columns from your original table.

Slicer = SUMMARIZE(Table1, Table1[Campagne], Table1[Contrat])

Now since you want the filtering to be done by Contrat and this would require a many-to-many relationship with the original table, you need to create a bridge table.

Contracts = VALUES(Table1[Contrat])

Now set up the relationships on Contrat making sure that the Slicer to Contracts relationship has cross-filtering enabled both ways.

Now you can use the Slicer[Campagne] column for your slicer and put Table1[Campagne] on your table and it should filter how you want.