Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
GracieLeeMonroe
Frequent Visitor

Help needed with COUNTIF in DAX

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. 

 

DiscountinvoiceNumberserviceMonthSum of TotalAllocatedDiscountperInvoiceLine

AdjustedAmountInvoiceLineItem

-6601234504/2021-6600

-660

-4001247805/20214000-200

3800

-2001247805/20212000-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

 

1 ACCEPTED SOLUTION
GracieLeeMonroe
Frequent Visitor

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

View solution in original post

2 REPLIES 2
v-cazheng-msft
Community Support
Community Support

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

GracieLeeMonroe
Frequent Visitor

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.