cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft

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
Microsoft

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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors