Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to create countifs in DAX - replicate Excel countifs

I'm trying to replicate Excel's countifs function. I've seen a couple other posts but I'm new to DAX and not totally clear how to do this.   How do I create a DAX formula that evaluates three separ...
Need Help
  • mhossain's avatar
    mhossain
    6 years ago

    Hi Anonymous 

     

    use below dax, it should work

     

    Countifs=
    var delivery = 'Table'[Delivery]
    var salesdoc = 'Table'[SalesDoc]
    var item = 'Table'[Item]

    RETURN

    COUNTROWS(
    FILTER(
    all('Table'),
    delivery='Table'[Delivery] &&
    salesdoc=''Table'[SalesDoc] &&
    item='Table'[Item]))

     

     

     

    Please change the table name as per your table name, please let me know if this works for you.

vanessafvg's avatar
vanessafvg
Community Champion
6 years ago
what are you trying to evaluate exactly, if you can say what you trying to do, then i can help, but i think what you might be needing is the filter() function.
Anonymous's avatar
Anonymous
Not applicable
6 years ago

My data has double counts in it for some columns but not others.

 

For the columns that have double counts I want to adjust the values (by creating a new column) to reflect the double counting. E.g., there are delivery expenses that show up multiple times. The delivery expense in total is $10...but the row is repeated three times so when it's summed together it's $30. I want to add another column that divides by how often there is double counting. So, to continue the example the # of double counts might be 3 so I take $30 / 3 = $10 so I can sum correctly.