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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
bjoshi
Resolver I
Resolver I

Using Dax to remove duplicates before summing up a column

Hello,

 

This is a dax query that I have used to create a measure. 

 CALCULATE(SUM(Invoice[AmountDueAUD]), Invoice[Type]="ACCREC")

The problem is that there are multiple rows with the same invoice number and each row has the same value in AmountDueAUD column. So I want to remove the duplicate invoices before summing up the AmountDueAUD column. I know I can remove the duplicates using "Remove Duplicates" option, but there are other charts in my report that require that multiple columns. So, I want to achieve removing duplicates using Dax. I tried this but didn't work:

CALCULATE(SUM(Invoice[AmountDueAUD]), DISTINCT(Invoice[InvoiceNumber]), Invoice[Type]="ACCREC")

Thank You

 

 

1 ACCEPTED SOLUTION
Phil_Seamark
Microsoft Employee
Microsoft Employee

You could try and remove duplicates via the Query Editor (which has an excellent function for removing duplicates)

 

Or in DAX you could create a summary table with just the lines you need

 

Summary Invoice Table = SUMMARIZECOLUMNS(
                'Invoice'[InvoiceNumber],
                --- Columns ---
                "AmountDueAUD",MAX('Invoice'[AmountDueAUD])
                )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Phil_Seamark
Microsoft Employee
Microsoft Employee

You could try and remove duplicates via the Query Editor (which has an excellent function for removing duplicates)

 

Or in DAX you could create a summary table with just the lines you need

 

Summary Invoice Table = SUMMARIZECOLUMNS(
                'Invoice'[InvoiceNumber],
                --- Columns ---
                "AmountDueAUD",MAX('Invoice'[AmountDueAUD])
                )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks @Phil_Seamark. This worked for me.

 

I wanted to do it without having to create a new table and also I couldn't do it via Query Editor because there were other charts that relied on the duplicates.

 

Please let me know if you come across something that solved this problem without having to create a new summary table.

 

Hi @bjoshi,

 

It probably is possible to do as a measure over your original table, but it's probably not good data modelling to have the same number on repeating lines that shouldn't be there in the first place.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.