Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Countif with multiple criteria

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 NameSale AmountRepeats
Netflix53
Google102
Apple153
Walmart52
Apple153
Google81
Netflix53
Walmart101
Apple153
Apple251
Walmart52
Netflix53
Google102

 

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!

Need Help
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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!

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    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