topic Re: COUNTIFS in DAX in DAX Commands and Tips

COUNTIFS in DAX

bidataservices — Tue, 14 Feb 2023 13:55:13 GMT

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.

Re: COUNTIFS in DAX

tamerj1 — Tue, 14 Feb 2023 14:07:18 GMT

Hi bidataservices

please try

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

Re: COUNTIFS in DAX

FreemanZ — Tue, 14 Feb 2023 14:27:17 GMT

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

)

Re: COUNTIFS in DAX

BrianConnelly — Tue, 14 Feb 2023 14:47:33 GMT

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

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

Re: COUNTIFS in DAX

BrianConnelly — Tue, 14 Feb 2023 15:08:43 GMT

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

Re: COUNTIFS in DAX

bidataservices — Wed, 15 Feb 2023 09:44:48 GMT

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?

Re: COUNTIFS in DAX

FreemanZ — Wed, 15 Feb 2023 12:16:36 GMT

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:

Re: COUNTIFS in DAX

FreemanZ — Wed, 15 Feb 2023 12:23:04 GMT

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.

Re: COUNTIFS in DAX

tamerj1 — Wed, 15 Feb 2023 13:26:05 GMT

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 )

Re: COUNTIFS in DAX

bidataservices — Thu, 16 Feb 2023 13:23:48 GMT

Thanks, that worked well.

What if I wanted to do a SUMIFS on sales?

Re: COUNTIFS in DAX

tamerj1 — Thu, 16 Feb 2023 13:30:43 GMT

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 )

Re: COUNTIFS in DAX

BrianConnelly — Mon, 20 Feb 2023 03:10:22 GMT

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.