Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |