Countif with multiple criteria

[Anonymous]

Hello, I'm working on a dataset which has Vendor IDs and Vendor Amount and would like to be able to count how many times the same vendor has made a sale for the same amount. Something like this:

Vendor Name	Sale Amount	Repeats
Netflix	5	3
Google	10	2
Apple	15	3
Walmart	5	2
Apple	15	3
Google	8	1
Netflix	5	3
Walmart	10	1
Apple	15	3
Apple	25	1
Walmart	5	2
Netflix	5	3
Google	10	2

The formula in Excel would be =COUNTIFS(VendorSales[[#All],[Vendor Name]],[@[Vendor Name]],VendorSales[[#All],[Sale amount]],[@[Sale amount]])

I'm thinking most of my problem lies on the @ [field] part of it cause I could use a calculate with a filter.

Thanks!

[TomMartens]

Hey,

this calculated column provides what you are looking for:

col Count = 
var thisVendor = 'Table1'[Vendor Name]
var thisSaleAmount = 'Table1'[Sale Amount]
return
CALCULATE(
    COUNTROWS('Table1')
    ,ALL('Table1')
    ,'Table1'[Vendor Name] = thisVendor
    ,'Table1'[Sale Amount] = thisSaleAmount
)

Just a little recap:
As there is no Filter Context present if the DAX statement of a calculated column gets evaluated it's possible to store the values of the columns Vendor Name and Sales Amount from the current row into variables. The first thing that happens if the CALCULATE formulat gets evaluated is the so called context transition, a Row Context is transformed into a Filter Context. COUNTROWS(...) just counts the number of rows. First all existing filter get removed using ALL and the filter for Vendor Name and Sales Amount get reapplied.

Please be aware of the "Default Summarization" of the calculated column, you might need it to change accordingly. From the Data view --> Menu Modeling --> Default Summarization (from the Properties ribbon)

Hopefully this is what you are looking for.

Regards,

Tom

[Anonymous]

Hi @Anonymous 
I think there are many ways to achieve this.

Here is my code:

Column = CALCULATE(COUNTROWS('T1'),
FILTER(T1,T1[Vendor Name] =  EARLIER(T1[Vendor Name])),
FILTER(T1,T1[Sale Amount] =  EARLIER(T1[Sale Amount])))

Thanks!