Pivot table against data connection to PowerBI Datamart (Dataset) and/or Azure Analysis Services doesn't seem to use or see the table relationships for query or filter contexts

Question

A Pivot table against a data connection (Get data) to PowerBI Datamart (Dataset) or Azure Analysis Services database doesn't seem to use or see the table relationships for query or filter contexts. For example, say you have a simple model (stored in PowerBI) where you have Products, Customers, and Orders tables. These tables have 1-to-many relationships (One Customer -> many orders, One Customer -> many Products). If you connect with PowerBI desktop and put a table visual showing all orders, and add a slicer that lists Customers, you can subsequently limit the data in the order visual by selecting a specific customer in the slicer (as expected). When creating a Pivot table in Excel against the same dataset, adding the order fields you'd like to see in the rows (fields list), and then subsequently creating a slicer for all customers, you won't get any data filtering affect in the Pivot table. It's hard to tell what can/should be done to make this work the same as PowerBI Desktop?? Anyone have insight into what's expected, or can be done to make this work properly?

Example:

I expected in the last step below for it to react like PowerBI Desktop (slicer limiting customers).. However PowerPivot seems to ignore the relationship between Customers and Orders??

Connect from PowerBI:

Add DataTable, Add Slicer:
Select customer in slicer to limit orders listed:
No Slicer (Customer Selected)

With Slicer Limiting Customers (only Orders from CustomerID 1 are shown in table)

Excel PowerPivot:

PowerPivot and Slicer added:

No Slicer Value selected:

Slicer Value selected (no impact on pivot table, all order values still listed):

Answer 1

Hi, @Craig Darling

You can join these two tables using VLOOKUP function and create a pivot table.

1. Join the two tables. You can use this article as a reference video-vlookup-when-and-how-to-use-it-9a86157a-5542-4148-a536-724823014785
   
2. Go to insert and click PivotChart
3. Set CustomerID as filters and put CustomerName, OrderID and Order in rows, then you can find Orders by selecting CustomerID.

   ---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in email-notifications to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Charles Qi_MSFT ,

VLOOKUP makes sense to use when all of the data is pulled into Excel and the join is done as a preparation step (where I basically hardcode the relationship between this data by combining it). I'm most interested in understanding how/if Excel handles joins (or rather does it even interpret the model relationships) when connecting to a cube in AAS or a dataset in PowerBI, like my example explains. "However PowerPivot seems to ignore the relationship between Customers and Orders??"