Show Me All Invoices Containing this Product

Level: Advanced

I was answering an interesting forum question last week, and as is often the case I thought it would make an interesting article to share with you all here.  I have modified the scenario a bit for this article and have built out some test measures to help explain some concepts.

Problem Definition

Here is the scenario.  The user wants to select a product from a list.  When that product is selected, they want to see a list of all invoices that contain that product AND ALSO see ALL OTHER PRODUCTS on those same invoices.  The reason this is such an interesting problem is that normally when you select products from a list in Power Pivot, the selected products filter the data model hence you can only see sales for those selected products.

Sample Data

I have produced the following data model in Power Pivot for Excel.  This solution can also work in Power BI of course.  You can see the products table is joined to the invoice table via Product ID (1 below) and you can see the sample data in the 2 tables on the right (2 below).

Why This is Tricky

As you can see in the image below, when a slicer is added to the product table and then you select an item in the slicer, the invoice detail table ONLY shows that single selected product.  But the requirement it to see ALL the items on ALL the invoices that contain that selected product.

Hopefully it is clear from the above image that the natural filtering from Power Pivot is working against producing the required solution, so there needs to be a different approach.

My Approach to a Working Solution

The most important thing to understand about Power Pivot is how filtering works.  As I have just outlined above, the natural filtering behaviour is that when a filter is placed on a lookup table (Products in this case), then that filter is propagated to the data table via the relationship.  The trick to solve this problem therefore involves the following 4 steps.

1. Stop the slicer filtering the data model
2. “Detect” what has been selected in the slicer
3. Create a virtual table of invoice numbers that contain the selected product(s) from step 2
4. Use this virtual table of invoice numbers as the filter to pass to the measure

Let me step through each of these 4 steps to build a working solution.

1. Stop the Slicer Filtering the Data Model

The solution I came up with here was to load a second copy of the products table and load this do the data model.

You can see in the image above that the ProductsDisconnected table has no relationship back to the Sales table.  This is perfectly valid in Power Pivot – tables do not have to be connected, hence the name “disconnected table”.

My new extended data model is as shown below.  The list of products in the slicer (1 below) come from the disconnected table, hence this slicer has no filtering affect on the report (pivot table).  The list of products in the pivot table come from the Products table (2 below).

Note: If you are using Power BI, you can change the way that a slicer interacts with a visual (turn off cross filtering).  This is not possible in Excel hence I have used the approach outlined above that works in both tools.

2. Detect Products Selected in Slicer

When the user clicks on the slicer, nothing happens (of course) because the slicer isn’t connected to the rest of the data model.  It is possible however to “harvest” the selections from the slicer using the VALUES function.  VALUES returns a virtual table that contains a unique list of all of the items in the current filter context (the selected items in the disconnected slicer in this case).  One of the challenges when using table functions in Power Pivot is that you can’t “see” the table, so it can be hard to get your head around what is going on.  Take the valid DAX formula below.

= VALUES(ProductsDisconnected[Descr])

If I try to write this valid formula as a measure and add it to the pivot table, it will throw an error.  You can’t add a “table” to a visual (eg a pivot table) because you can’t put a table into a single cell in a visual.  A cell expects a scalar value, not a table (which is a 2 dimensional object).  What I like to do (to help visualise what is happening) is write test measures so I can “see” what is going on.  All I have to do is turn the table returned by VALUES into a scalar value.  Here is my test measure.

Selected Products = 
      CONCATENATEX(
           VALUES(ProductsDisconnected[Descr]), 
           ProductsDisconnected[Descr], 
           ", "
      )

As you can see below, the measure “Selected Products” now displays all the values selected in the slicer courtesy of the CONCATENATEX test function – confirmation that the VALUES function indeed returns a table of the selected items.

3. Create Table of Invoice Numbers

Now that I have successfully harvested the products that are selected by the user using VALUES, it is a matter of creating a virtual table of the invoice numbers that contain those products.  I am sure there are many ways to do this, but the solution I came up with is as follows.  Keep in mind this code won’t work as is – this code produces a table, and a table can’t be added to a Pivot Table (as mentioned above).

= CALCULATETABLE(
      VALUES(Sales[Inv #]),
      INTERSECT(
         ALL(Products[Descr]), 
         VALUES(ProductsDisconnected[Descr])
      )
  )

You can see in line 4 above that I am using the INTERSECT function.  I am passing the list of selected items from the slicer to the Products[Descr] column in the Products table.  The Products table is connected to the Sales table. So after this filter is passed to the Products table, the CALCULATETABLE function (line 1) propagates this filter to the Sales table, and then VALUES (line 3 above) extracts a list of the Invoice numbers that contain these selected products.  Importantly, this new virtual table created by CALCULATETABLE and VALUES (line 3) retains lineage to the data model.  What this means is that the new virtual table has a virtual one to many relationship between the virtual table and the table from where it was born (Sales in this case).  If this new virtual table created by CALCULATETABLE is then used as a filter function inside a CALCULATE, it will have the result of filtering the Sales table for the products in the list (in the virtual table).

4. Use the Table of Invoice Numbers as the Filter

The last step is to use this virtual table above (the list of invoice numbers) as a filter to return the sales for each item. Here is my final formula.

= CALCULATE(
      [Total Qty],
      CALCULATETABLE(
          VALUES(Sales[Inv #]),
          INTERSECT(
             ALL(Products[Descr]), 
             VALUES(ProductsDisconnected[Descr])
          )
      )
  )

This time I am using the table created by CALCULATETABLE (lines 4 – 7) as a filter for a CALCULATE.  After the virtual table is applied, CALCULATE then extracts the total sales quantity for all items on the invoices that contain the selected product.

You can download my sample workbook here if you would like to take a closer look => Sample Workbook