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
1	InvoiceId	InvoiceDate	ProductID	Price	ProductName
2	1	01/01/2012	1	100	a
3	1	01/01/2012	2	50	b
4	2	02/02/2012	1	100	a
5	2	02/02/2012	4	25	d
6	3	03/03/2012	1	100	a
7	3	03/03/2012	2	50	b
8	3	03/03/2012	5	25	e

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Facturas​

The result I want is the following

	H	I
2	Jan	1
3	Feb	0
4	March	1

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</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]

Your problem is that you do NOT want an AND, you want an OR. You want Name="Scott" or Name="Anderson"... cuz nobody has the name Scott AND Anderson.

=calculate(distinctcount(Invoices[InvoiceId]), FILTER(ALL(Invoices), Invoices[ProdId]=1 || Invoices[ProdId]=2))

 

[MrAnderson]

thanks Scott!

it doesn't seem to work though, if I follow your formula the result I get is "3" when it should be 2, maybe I didn't present the problem accurately, what I want is "check every invoice and for each invoice check if there are prodID 1 and prodID 2, if so, count that invoice"

thanks again.

Lucas.

 

[XLBob]

Below should give you what you want. Probably this is not the most elegant solution, but it's what I could come up with at the moment


-- removed inline image ---

 

[XLBob]

CALCULATE(DISTINCTCOUNT(Table1[InvoiceId]),FILTER(ALL(Table1[InvoiceId]),AND(CALCULATE(COUNT(Table1[InvoiceId]),Table1[ProductID]=1)>0,CALCULATE(COUNT(Table1[InvoiceId]),Table1[ProductID]=2)>0)))

can someone tell me how to paste the DAX Format beautified formula

 

[scottsen]

I just went to Dax Formatter by SQLBI and pasted it in, ... then copied the html version here...

=<br><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">DISTINCTCOUNT</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> Table1[InvoiceId] <span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">ALL</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> Table1[InvoiceId] <span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">AND</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">COUNT</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> Table1[InvoiceId] <span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent8">        </span><span class="indent8">        </span>Table1[ProductID] = <span class="Number" style="color:#EE7F18">1</span><br><span class="indent8">        </span><span class="indent4">    </span><span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent8">        </span><span class="indent8">        </span>> <span class="Number" style="color:#EE7F18">0</span>,<br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">COUNT</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> Table1[InvoiceId] <span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent8">        </span><span class="indent8">        </span>Table1[ProductID] = <span class="Number" style="color:#EE7F18">2</span><br><span class="indent8">        </span><span class="indent4">    </span><span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent8">        </span><span class="indent8">        </span>> <span class="Number" style="color:#EE7F18">0</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="Parenthesis" style="color:#D0D0D0">)</span><br>

 

[scottsen]

And apologies for mis-reading your question. Badly

Scott's Maybe Overly Verbose Solution:

Entries In Invoice:=CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[InvoiceId]))
Product1 Entries:=CALCULATE([Entries In Invoice], FILTER(Table1, Table1[ProductID] = 1))
Product2 Entries:=CALCULATE([Entries In Invoice], FILTER(Table1, Table1[ProductID] = 2))
Invoices with 1 and 2:=IF (CALCULATE([Entries In Invoice], FILTER(VALUES(Table1[InvoiceId]), [Product1 Entries] > 0 && [Product2 Entries] > 0)) > 0, 1, 0)

 

[MrAnderson]

thanks both of you! I'm way closer to what I want to do. I'll report back once I'm finished

 

[MrAnderson]

Hi,

sorry to open this thread again, but I was asked a new requirement with the same dataset, in this case, I need to SUM the price of both the products. I have modified the formula provided by XLBob, changing DISTINCTCOUNT for SUM:
=
CALCULATE (
SUM ( Invoices[Price] ),
FILTER (
ALL ( Invoices[ProductID] ),
AND (
CALCULATE (
COUNT ( Invoices[InvoiceId] ),
Invoices[ProductID] = 1
)
> 0,
CALCULATE (
COUNT ( Invoices[InvoiceId] ),
Invoices[ProductID] = 2
)
> 0
)
)
)


it works only in 2 of the three cases

1. it sums the correct amount when there is an invoice that has only both the target products
2. it sums zero when there is an invoice with one or none of the 2 target products
3. it doesn't sum correctly in the cases where there is an invoice that has the 2 target products and one or more alternative products, in those cases it sums all the amounts in the invoice, including products that are not the target products. So in the example I posted in the first post what I get is:

	H	I
2	Jan	$150
3	Feb	$0
4	March	$175

<tbody>
</tbody>

and what I'm looking for is

	H	I
2	Jan	$150
3	Feb	$0
4	March	$150

<tbody>
</tbody>

thanks again for all the help!

 

[XLBob]

This one is tricky. I need to think really hard. In the meantime, see if Scottsen can come up with something. Actually I have also posted a question that I kind of hope Scottsen can offer some help.