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