Countrows of data with only one row

[Anonymous]

Hi,

i have a dataset like this:

Order ID / Product ID

1 / P1

1 / P2

2 / P3

3 / P1

3 / P3

So if a customer has different products in his checkout, there will be a row for each product. So some orders have only one row, some more.

Now i want to analyze on every product id how many orders have only purchased this product.

And of course i need a measure of how many orders i have for every product combination (P1 and P2, P2 and P3, P1 and P3, all 3 Products).

I'm not so much into DAX, so thank you for your help.

Best,

Micha

[v-eachen-msft]

Hi @Anonymous ,

You can create a calculated column “CombinePID”  to meet your requirement.

CombinePID = CALCULATE(CONCATENATEX('Table',[Product ID],"&"),ALLEXCEPT('Table','Table'[Order ID]))

To meet your first requirement you should create a calculated column first.

OnlyOne =
CALCULATE (
    COUNTROWS ( DISTINCT ( 'Table'[Order ID] ) ),
    FILTER ( 'Table', NOT ( CONTAINSSTRING ( 'Table'[CombinePID], "&" ) ) )
)

Then use "CombinePID" and "OnlyOne" to create a visual.

To meet your first requirement you should create the following calculated column.

CombinePIDNum =
CALCULATE (
    COUNTROWS ( DISTINCT ( 'Table'[Order ID] ) ),
    FILTER ( 'Table', CONTAINSSTRING ( 'Table'[CombinePID], "&" ) )
)

Then use "CombinePID" and "CombinePIDNum" to create a visual.

Best Regards,

Eads

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-eachen-msft ,

thank you, it's going in the right direction, but still not working for me.

So i created all the columns and created a table with "CombinePID" and "Only One" and "CombinePIDNum".

The result is that it's the same for alle product ID Combinations.

Also following problem: Let's say i have a transaction with one order_id and two product ids. The result in "combinePIDNum" is 2. But it should be 1, because it is one transaction.

Do you have an idea?

Thank you in advance.