Filtering/aggregating across two joins

[NetJer20]

Power BI has a capability I do not understand. I'm getting transactional data to successfully aggregate based on a field that is two joins away, and it shouldn't work. I'm wondering if anybody knows about any Power BI enhanced relationship/join management on the backend of Power BI. Here's my situation:

We have a "customer" table. This table is joined in a two way filtering, one-many relationship to a "marketplace" table, aka Europe, US, Canada, etc. I put a slicer on the canvas with marketplace (id field), and build a table of customer info, and that marketplace Id slicer will filter the customer data, pretty straightforward. 

Now, I join a sales table to that customer table, one-many relationship (one customer to many sales), and one directional filtering from customer to sales. Joins look like: Marketplace<->Customer->Sales.

Here's where it gets weird. I build a new table with the marketplace Id from the marketplace table, customer Id from the customer table, and amount from the sales table. The amounts are summed up to the brand and marketplace. How is this!?!? How can it aggregate on the fly across these two joins? I cannot recreate this in SQL. If I select the same three fields with the same joins, I get a duplicated sum of amounts for the marketplaceId and customerId. But yet Power BI, it aggregates correctly.

[parry2k]

@NetJer20 it is working because the customer table is filtering both market place and sales if you remove customer id from the visual, marketplace, and sales will not work until you have a market place and customer relationship cross filter set to both.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[mahoneypat]

This sounds like the expected behavior for Power BI.  Your marketplace table is filtering the customer table (albeit with a bi-di relationship) and the customer table filters sales.  So using columns from the marketplace and/or customer tables will filter the sales (prior to the aggregation/measure) to give you the expected results.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat