count invoices with multiple AND filters

[MrAnderson]

Hi,

I’m trying to count the number of instances where a customer has bought 2 particular products in the same invoice. Let’s assume I have the following table called "invoices.

	A	B	C	D	E
InvoiceId	InvoiceDate	ProductID	Price	ProductName
a
b
a
d
a
b
e

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]01/01/2012[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]01/01/2012[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]02/02/2012[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]02/02/2012[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]03/03/2012[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]03/03/2012[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]03/03/2012[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]

</tbody>

Facturas​

The result I want is the following

	H	I
Jan
Feb
March

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

</tbody>

Facturas​

I have tried with the CALCULATE function with these formulae:
=calculate(distinctcount(Invoices[InvoiceId],Invoices[ProdId]=1;Invoices[ProdId]=2)
Or
=calculate(distinctcount(Invoices[InvoiceId],Invoices[ProdId]=1&&Invoices[ProdId]=2)

but I get no results, can anyone give a hand?

thanks a lot!.

 

[scottsen]

CALCULATE can take any number of filter params. You currently have one which is "which invoices do we even consider?". Now you want to add another which is "Only include Products 1 or 2".

That reads okay, and maybe it is correct!?

FILTER(ALL(Invoices[ProductId]), Invoices[ProductId] = 1 || Invoices[ProductId] = 2)

(This would be your 3rd param to the outer calculate)

 

[XLBob]

Sweet, that's how you can do it. Didn't think of that. I was thinking putting the logic into one filter expression

 

[MrAnderson]

thanks again guys!

I actually have solved what I wanted and much more thanks to this article Basket Analysis – Dax Patterns, you can see a different approach there.

best