0 votes
1 view
in BI by (17.6k points)

I have a Transactions table with the following structure:

ID  | Product | OrderCode | Value

1   |   8     | ABC       | 100

2   |   5     | ABC       | 150

3   |   4     | ABC       |  80

4   |   5     | XPT       | 100

5   |   6     | XPT       | 100

6   |   8     | XPT       | 100

7   |   5     | XYZ       | 100

8   |   8     | UYI       |  90

How do I create a table (or list) with the order codes of orders with both products 5 and 8?

In the example above it should be the orders ABC and XPT. 

closed

1 Answer

0 votes
by (47.2k points)
selected by
 
Best answer

Actually, there are many ways to do this, but here's a general solution what I came up with:

FilteredList =

    VAR ProductList = {5, 8}

    VAR SummaryTable = SUMMARIZE(Transactions,

                           Transactions[OrderCode],

                           "Test",

                           COUNTROWS(INTERSECT(ProductList, VALUES(Transactions[Product])))

                               = COUNTROWS(ProductList))

    RETURN SELECTCOLUMNS(FILTER(SummaryTable, [Test]), "OrderCode", Transactions[OrderCode])

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...