Forum Discussion

bidataservices's avatar
bidataservices
Frequent Visitor
3 years ago

COUNTIFS in DAX

Can anybody please help with a DAX question. I have a report in Excel that counts the items that make up an invoice. I have atached a screenshot with the Excel formula highlighted. I am trying to rec...
CountIfs
DAX
BrianConnelly's avatar
BrianConnelly
Icon for Resolver III rankResolver III
3 years ago

You could consider creating a calculated column as well which then you could build a measure on...

 

Items = 
VAR CountV = CALCULATE(COUNTA(Inv[InvoiceNo ]),FILTER('Inv','Inv'[Category] = EARLIER('Inv'[Category]) && 'Inv'[Document Item] = EARLIER('Inv'[Document Item]) && Inv[Subcategory]=EARLIER('Inv'[Subcategory])))
RETURN IF('Inv'[InvoiceType ]= "Invoice",CountV,BLANK())

 

 

If you don't want to create a calculated column you could use the same formula to create a Summarize Table variable with SUMX to bring directly into a measure.

Items Total = 
VAR tTable = SUMMARIZE('Inv',Inv[Cutomer ],Inv[Category],Inv[Subcategory],Inv[Document Item],"Items",SUMX('Inv',IF(Inv[InvoiceType ]="Item",1,0)))
RETURN SUMX(tTable,[Items])
  • bidataservices's avatar
    bidataservices
    Frequent Visitor
    3 years ago

    Thanks, I have tried the calculated column and it is nearly there but not quite right. As you can see from the results below. It should be counting 3 items, as one of the locations is Manchester. How does the EARLIER function work?

    • BrianConnelly's avatar
      BrianConnelly
      Icon for Resolver III rankResolver III
      3 years ago

      Just add another condition, 

       && Inv[City]=EARLIER('Inv'[City])

      You will want to add any conditions that make the record the same as the previous.  If my anyswer helped, please mark it.