Help needed with COUNTIF in DAX

GracieLeeMonroe — Wed, 27 Oct 2021 15:47:05 GMT

All

Problem:

I need to allocate the discount to invoice line items, but there are cases when the discount is credited in separate credit memo and has only one invoice line as a result. Then I need to make sure that those lines will be summarized in my column called "AdjustedAmountInvoiceLineItem" using countif function. I tried several DAX commands like COUNT, COUNTA, COUNTROWS in combination with FILTER, ALL, ALLEXCEPT to no avail so far.

Discount	invoiceNumber	serviceMonth	Sum of Total	AllocatedDiscountperInvoiceLine	AdjustedAmountInvoiceLineItem
-660	12345	04/2021	-660	0	-660
-400	12478	05/2021	4000	-200	3800
-200	12478	05/2021	2000	-50	1950

Right now, the value in the last column is zero, but should be -660 EUR.

So this is my current DAX Command:

AdjustedAmountInvoiceLineItem = IF(AND(Table1[Discount]="Discount", Calculate(COUNT,ALLEXCEPT(Table1, Table1[invoiceNumber], Table1[serviceMonth])>1)),0,Table1[Sum of Total]+Table1[AllocatedDiscountPerInvoiceLine])

But I get this error message:

The syntax for ',' is incorrect. (DAX(IF(AND(Table1[Discount]="Discount",Calculate(COUNT,ALLEXCEPT(Table1[invoiceNumber], Table1[serviceMonth])>1)),0,Table1[Sum of Total]+Table1[AllocatedDiscountPerInvoiceLine]))).

I cannot see the error and I am not sure if this is the correct DAX command at all.

I appreciate all the help!

Thanks!

GracieLee

Re: Help needed with COUNTIF in DAX

GracieLeeMonroe — Thu, 28 Oct 2021 10:13:21 GMT

All,

found it, here is the DAX Command I used:

IF(AND(Table1[Discount]="Discount", Calculate(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[serviceMonth],Table1[invoiceNumber]))>1),0, Table1[Sum of Total]+Table1[AllocatedDiscountPerInvoiceLine])

Have a great day!

Regards

GracieLee

Re: Help needed with COUNTIF in DAX

v-cazheng-msft — Mon, 01 Nov 2021 03:49:48 GMT

Hi GracieLeeMonroe

Noticed that you have resolved this issue by yourself. Could you please kindly mark your solution to help the other members find it more quickly? Thanks in advance!

Best Regards,

Community Support Team _Caiyun

topic Help needed with COUNTIF in DAX in DAX Commands and Tips

Help needed with COUNTIF in DAX

Re: Help needed with COUNTIF in DAX

Re: Help needed with COUNTIF in DAX