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
Solved! Go to Solution.
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]) )
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]) )
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.
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!
User | Count |
---|---|
123 | |
74 | |
66 | |
53 | |
53 |
User | Count |
---|---|
199 | |
104 | |
88 | |
79 | |
77 |