Forum Discussion

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

Community 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

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