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

image

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.

Video_2018-04-25_074324

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.

image

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

image

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.

selected products

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).
image

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 (shown in the red box as 1 above) 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.

image

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.

final slicer

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

Share?

Comments

  1. A performance optimized alternative=
    CALCULATE (
    [Total Qty],
    CALCULATETABLE (
    VALUES ( Sales[Inv #] ),
    TREATAS ( VALUES ( ProductsDisconnected[Descr] ), Products[Descr] )
    )
    )

    However, this measure does only work in Power BI and Excel 2016.

  2. Ooohh dear!!Fantastic!……you solved my puzzle!Thanks Matt for the solution and detail explanation for the question I posted last week. I have never used intersect. Will try this solution and let you know how I go.

  3. Very smart use of virtual tables, INTERSECT and CONCATENATEX. Thank you Matt for sharing your advanced knowledge of DAX in a most clear and concise language. Love your approach.

  4. More like a doctor’s prescription for the troubles I am going through at work! Thanks doc. Matt!

  5. I added a calculatedColumn in sales table named ‘descript’ with formula ‘Sales'[descript] = related(product[descr])

    and added a slicer based on product[descr]

    then draged the sales[Inv#] and sales[discript] columns into row area of pivot table

    with the measure below, it got the same results.

    Sales of ALL Invs:=
    VAR InvlistofSelectedProduct =
    CALCULATETABLE ( VALUES ( ‘Sales'[Inv#] ), ALL ( ‘Sales'[Inv#], ‘Sales'[descript] ) )
    RETURN
    CALCULATE (
    [Total Qty],
    ALL ( ‘Product'[descr] ),
    KEEPFILTERS(InvlistofSelectedProduct)
    )

    By the way above, it dosen’t need a disconnected table but the relationship between Product and Sales.

  6. Hello! What about this note
    Note: If you are using Power BI, you can change the way that a slicer interacts with a visual (turn off cross filtering).
    I tried to go this way but unsuccessfully. As far as I understand, in this solution we don’t have to make copy of Products table, but how can we modify last formula with only one table and no-interactive slicer?

  7. That’s a very elegant way to work the filter context when you don’t have access to TREATAS like in Excel 2016 Matt. I’m duly impressed.

  8. Here is my solution (from Chinese dax learner 🙂 )
    All Sales on Invoice2:=CALCULATE (
    [Total Qty],
    CALCULATETABLE (
    VALUES ( Sales[Inv #] ),
    filter(all(‘Products’),CONTAINS(VALUES(‘ProductsDisconnected'[Descr]),’ProductsDisconnected'[Descr],’Products'[Descr])
    ))
    )

  9. Rockstar! How well does this scale to >300 k rows? I think this would adapt well to a disconnected Chart of accounts where you want to see all lines of transactions contain Account 4xx

  10. Hi. Awesome example, managed to implement successfully. I have been trying to enhance it and try an only show the selected items for each invoice. Failing dismally. any thoughts?

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x