cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filtering/aggregating across two joins

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.



2 REPLIES 2
Highlighted
Super User VI
Super User VI

Re: Filtering/aggregating across two joins

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Super User VII
Super User VII

Re: Filtering/aggregating across two joins

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors