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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors