returning a list of values based on another list of filtered values

[CL7777]

Hello, I consider myself an advanced beginner dax user.

Today, I am working on a problem I cannot solve. I have a table (lets call it "part data" that contains information about sales of a part (order number, part number, revenue, etc..).. The order column has many duplicate values (for example, when the order had more than one part on it, it would appear multiple times). What I would like to do is (1) find the order numbers for any orders that contain a part number that has a "PKG" at the end of the part number. I would then like to take those order numbers and in turn, return a new table that has the oder number and all part numbers that appear on those orders (regardless of if they have a PKG at the end or not). The end product would be a table with orders (appearing multiple times because of the multiple parts on the order and the part numbers).. the return table would look something like this:

order   part number

A        100-PKG

A         101

A         102

B          abc-PKG

B          abc1

C         xxx-PKG

C         101a

C         bn-13

C         bc22

So, I know I have to create an intermediate table that has all order numbers on there that end in PKG, and then I need to take that table (or list of values) and return a table with those orders and all part numbers on those orders. I think I can do the first step, but its tying together the two steps that is confusing to me (in one dax expression)

Any help would be much appreciated,

[jdbuchanan71 · Accepted solution]

Hello @CL7777 

This should get you what you are looking for.  First we generate a list of just the orders where the last 3 of the part number contains the "PKG" then we use that list to filter the entire PartData table to generate the new table

PKG Orders = 
CALCULATETABLE (
    PartData,
    CALCULATETABLE(
        VALUES ( PartData[Order] ),
        CONTAINSSTRING ( RIGHT ( PartData[Part Number], 3 ), "PKG" )
    )
)

I have attached my sample file for you to take a look at.

If this solves your issues please mark it as the solution. Kudos 👍 are nice too.

[CL7777]

It seems so simple when one sees the solution, very elegant, thanks!