How to create a table (or list) with the order codes of orders with both products

[webportal]

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 orders ABC and XPT.

Thank you!

[v-yulgu-msft]

Hi @webportal,

You can new a calculated table:

New Table =
FILTER (
    Table,
    CALCULATE (
        DISTINCTCOUNT ( Table[Product] ),
        FILTER ( ALLEXCEPT ( Table, Table[OrderCode] ), Table[Product] = 5 )
    )
        + CALCULATE (
            DISTINCTCOUNT ( Table[Product] ),
            FILTER ( ALLEXCEPT ( Table, Table[OrderCode] ), Table[Product] = 8 )
        )
        >= 2
)

Regards,

Yuliana Gu

[Ashish_Mathur]

Hi,

What is your expected result?

[webportal]

@Ashish_Mathur
Similar to the solution presented above, although the visual should be a table instead of a comma separated string.

[Greg_Deckler · Accepted solution]

I might look at this in more detail but at first glance it looks very similar to the problem that I wrote my "Patient Cohort" Quick Measure to solve.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Patient-Cohort/m-p/391883

[webportal]

@Greg_Deckler this works very well, thanks for sharing!

But how to return tmpTable3 as a table instead of a card with a comma separated string? If there are many patients in the cohort (orders in my case) the card won't do.

Thank you so much for helping!

[Stachu]

@webportal
if you use UNICHAR(10) (line break) instead of "," as separator it CONCATENATEX it should improve readability, still it's not a table per se. just a workaround

[webportal]

Good idea, thanks a lot!
🙂

[Greg_Deckler]

I will look into that. I really, really, really wish that table and matrix visualizations accepted a measure that returned a table as a valid input. That would solve sooooooo many problems...

[webportal]

Hi,
Yes, this is very similar to the problem I have in hands.
Your solution seems perfect, although I'll need a bit of thinking to fully understand it.
Thanks a lot for sharing this!