COUNTIFS in DAX

[bidataservices]

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.  

[tamerj1]

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]

Thanks, that worked well.

What if I wanted to do a SUMIFS on sales?

[tamerj1]

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

[BrianConnelly]

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]

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]

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.

[BrianConnelly]

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"

[FreemanZ]

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]

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:

[tamerj1]

Hi @bidataservices 

please try

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

[FreemanZ]

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.