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 recreate this report in Power BI but struggling with the DAX for this measure.  

CountIfs
DAX

11 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi bidataservices 

    please try

    Item Count =
    IF (
    'Table'[Invoice Type] <> "Item",
    COUNTROWS (
    CALCULATETABLE (
    'Table',
    ALLEXCEPT ( 'Table', 'Table'[Document], 'Table'[Item], 'Table'[City] )
    )
    )
    )

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      hi bidataservices 

      tried to verify tamerj's code, it worked like:

      Item Count = 
IF (
    TableName[InvoiceType] <> "Item",
    COUNTROWS (
        CALCULATETABLE (
            TableName,
            TableName[InvoiceType] = "Item",
            ALLEXCEPT ( TableName, TableName[Document], TableName[Item], TableName[City] )
        )
    )
)

       

       

      With CALCULATETABLE+ALLEXCEPT, the code is much more concise. 

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi bidataservices 

     

    try like:

    column =
    VAR _count =
    COUNTROWS(
        FILTER(
           TableName,
           TableName[InvoiceType]="Item"
    &&TableName[InvoiceType]=EARLIER(TableName[InvoiceType])
    &&TableName[Document]=EARLIER(TableName[Document])
    &&TableName[Item]=EARLIER(TableName[Item])
    &&TableName[City]=EARLIER(TableName[City])
        )
    )
    RETURN
    IF(
       [InvoiceType]<>"Item",
       _count
       )
    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      hi bidataservices 

      try like:

      column = 
VAR _count =
COUNTROWS(
    FILTER(
       TableName,
       TableName[InvoiceType]="Item"
        &&TableName[Document]=EARLIER(TableName[Document])
        &&TableName[Item]=EARLIER(TableName[Item])
        &&TableName[City]=EARLIER(TableName[City])
    )
)
RETURN
IF(
   [InvoiceType]<>"Item",
   _count
)

       

      it worked like:

       

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

    Have you considered pulling the data in and using Power Query to Group and Sum.  Steps would be in Power Query:

    1. Create a Conditional Column that says if InvoiceType = Item then 1 else 0
    2. Group by all the fields required and then Sum the conditional column and call it "Items"

     

  • 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.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi bidataservices 
    Please refer to attached sample file with the solution

    Item Count = 
IF (
    'Table'[InvoiceType] <> "Item",
    COUNTROWS (
        CALCULATETABLE (
            'Table',
            'Table'[InvoiceType] = "Item",
            ALLEXCEPT ( 'Table', 'Table'[Document], 'Table'[Item], 'Table'[City] )
        )
    ) + 0
)
    • bidataservices's avatar
      bidataservices
      Frequent Visitor
      3 years ago

      Thanks, that worked well.

      What if I wanted to do a SUMIFS on sales?

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        bidataservices 
        You mean like this?

         

        Item Sales = 
IF (
    'Table'[InvoiceType] <> "Item",
    SUMX (
        CALCULATETABLE (
            'Table',
            'Table'[InvoiceType] = "Invoice",
            ALLEXCEPT ( 'Table', 'Table'[Document], 'Table'[Item], 'Table'[City] )
        ),
        'Table'[Sales]
    ) + 0
)