Filtering during CALCULATETABLE using values from another table (DAX)

Question

I have two tables in a Power BI workspace:

Table 1 is derived from a query that extracts a list of orders and the products within them.

CustomerID    OrderID    ProductID    Country    Date
0010234       9235064    V24-GY       UK         04/07/2018
0010234       9235064    AB5-84       UK         04/07/2018
0010234       9235064    RIM-3S       UK         04/07/2018
0010234       4812623    V79-GY       UK         27/09/2018
0049371       7924823    V24-GY       UK         09/10/2018
0049371       7924823    59H-PW       UK         09/10/2018

Table 2 contains a list of unique OrderID values that included a certain product, X (e.g. V24-GY). This was created using SUMMARIZECOLUMNS and applying a FILTER that searched for product X in Table 1

OrderID
9235064
7924823

I would like to use the OrderID values in Table 2 as a filter for Table 1 so that I can create a new table, Table 3.

CustomerID    OrderID    ProductID    Country    Date
0010234       9235064    V24-GY       UK         04/07/2018
0010234       9235064    AB5-84       UK         04/07/2018
0010234       9235064    RIM-3S       UK         04/07/2018
0049371       7924823    V24-GY       UK         09/10/2018
0049371       7924823    59H-PW       UK         09/10/2018

I've tried to use CALCULATETABLE with FILTER but Table 2 isn't recognized as a valid name/entity and so can't be used.

How can I get to Table 3?

Do you need to have Table 2 separately or is it just an intermediate step to get Table 3? — Alexis Olson
@AlexisOlson - I created it as an intermediate step in my journey to create Table 3 — PuzzledInVA
In that case, it's may not be best to create it as it's own calculated table. Can you outline what you intend the end result Table 3 to look like? It's possible you can skip the intermediate step altogether. — Alexis Olson
Thanks @AlexisOlson. What I want to get is a table that shows me the contents of all orders where at least 1 of product X was purchased. In a way it's a type of affinity matrix (I've been looking at market basket techniques too). — PuzzledInVA
Yeah, pretty sure you don't need an intermediate table, but it's quite difficult to say for sure since you haven't shown any example tables. A mcve would be very helpful in answering this. — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2018-07-24T20:54:12

You can do this where you only calculate Table2 as a variable instead of a calculated table that shows up in your data model.

Table3 can be calculated as follows:

 Table3 = 
     VAR Table2 = SUMMARIZECOLUMNS(Table1[OrderID],
                      FILTER(Table1, Table1[ProductID] = "V24-GY"))
     RETURN FILTER(Table1, Table1[OrderID] IN Table2)

Here's a similar method that skips the summarizing.

Table3 = FILTER(Table1,
             Table1[OrderID] IN
                 SELECTCOLUMNS(
                     FILTER(Table1, Table1[ProductID] = "V24-GY"),
                     "OrderID", Table1[OrderID]))

Filtering during CALCULATETABLE using values from another table (DAX)

1 Answers