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