I think I have a relationship problem (only in PowerBI ;)

[inservo]

Dear PowerBI users,

I have the following situation: 

I have a sales database (in Excel) that I connected to PowerBI. This holds sales order line items including customers.

I also have a contract database (also in Excel). This holds frame contracts with customers. 

I now have an overview page where I can see all my sales per region, product etc. In that overview page, I do have a list of my top20 biggest customers. I created a drill through page for customer details (address, office locations etc) that all come from my sales database. 

Now I want to create a second drill through page with contract details. So I did the following:

1. Imported the contracts database

2. In the relationship editor connected the customer ID. As both databases could hold multiple lines with the same customer ID I chose a many to many relationship (eventually that is my problem, not sure though...)

3. I duplicated my details page (see above) and now want to have my customer ID from the sales DB (as this - per my understanding defines the drill through) and the rest of the information from the contracts database.

However, as soon as add details from the contracts database to my table visual, everything disappears aka an empty table. I thought through defining the n:n relationship in the editor, I told PowerBI that those to columns (customer ID in both databases) determine the common denominator.

Can someone give me a hint on how to set something like this up?

Thank you very much in advance!

[inservo]

ok, this worked. But now I have the problem, that my drill throughs don't work anymore. I corrected all of the existing visuals to the new dimension table columns. 

And it all worked for ONE detail page. But I want to drill through to several "sub pages". This somehow does not work for me. 

I have a master table visual (let's say biggest customers) and have a set up a button with the action "drill through" to pages. For the first page it works. Then I duplicated that page and changed the visuals to show some other details and now my drill through only works on the first detail page.

I though PowerBI would allow to drill through to multiple pages...

[johnt75]

It can work for multiple drill through pages. You might need to turn off "keep all filters" in the drillthrough well. Also, make sure that on your duplicated pages there are no additional fields in the drill through well - they could be remnants from the original page.

[inservo]

Hmmm.. doesn't work for me. No matter if I keep Keep All Filter on or off, it simply does not drill through to the second page. Always only to the first.

I also want to go from the 1st to the 2nd detail page (DT page). Could that cause any issues?

[johnt75]

It shouldn't do. I've created a report where I can drill from page 1 to either page 2 or page 3, and from page 2 to page 3. You just need to make sure that the column from the dimension table is present in the visuals you want to drill through from. Not tried it from a button so I'm not sure how that column would get passed through.

[inservo]

Ok, after doing some more research, I found out that a step wise Drill Through in PowerBI is not foreseen in the Drill Through feature.

However, this post shows an awesome easy way to do it using synced slicers: https://community.powerbi.com/t5/Desktop/Drill-through-Go-from-one-detail-page-to-another-without-go...

[inservo]

Thank you for your response @johnt75 

So this means, I will basically outsource everything into (many) dimension tables that is not core data? E. g. customer addresses might go into the customer dimension table? 

[johnt75]

Exactly. This will make the whole thing much more efficient and easier to maintain.

[johnt75 · Accepted solution]

Create a customer dimension table from the unique values in the sales and contracts data. You can either do this in Power Query or in DAX. Link this new table to both sales and contracts in one-to-many relationships and always use values from the new customer dimension table in all visuals, slicers, drill throughs etc.