Removing duplicates using DAX for Month over Month datasets

[Antonio195754]

I have excel files a client is trying to summarize using PBI.  The current process is, each week a new file is produced, users goes to a column of "account IDs", removes duplicates and then they get the count of from another column, easy.  So they want a historical view and my thought was then to combine the tables, add a date column and respectively label that column by the date it was produced.  But now what i'm seeing is, if i remove dups in the query editor, i'm finding that the account ID could be and is the same from one file to another so the output is the last month is accurate, but the prior months have hardly any counts b/c the editor did what it was told to do, remove duplicates.  So my question is, how can i set up a measure that says something like "only remove duplicates per the month column, not the entire table."

Below is what the table looks like basically, where the output i'd like to see for december '21, after removing dups, the count is 5. And for november the count should be 6 but bc my query removes any duplicate as it should, the output shows 0.  Is there a way to make PBI filter by a column so that it doesn't remove all?

My plan B was to have the client remove duplicates in each file, then send over, that would certainly work.  But i'm trying to simplify this as much as possible if PBI has a way to do this.

account ID	Count of value	Date
1	1	12-2021
2	4	12-2021
2	0	12-2021
1	2	11-2021
2	4	11-2021
2	4	11-2021

[Antonio195754]

To add, what client sends me is the file not massaged, so client doesn't remove duplicates. it's just the raw data where we were looking for PBI to do the heavy lifting. All client does is append the masterfile with new data and adds the date in the date column, and PBI picks it up from there.

[Anonymous · Accepted solution]

Hi @Antonio195754 ,

I think you can suggest your client to remove duplicates in Power Query first, then sum the count.

If they only want to calculate result by dax without transforming their table, you can try code as below to create a measure.

Sum count (Removing duplicates) = 
VAR _Removing_duplicates =
    SUMMARIZE (
        'Table',
        'Table'[account ID],
        'Table'[Count of value],
        'Table'[Date]
    )
RETURN
    SUMX ( _Removing_duplicates, [Count of value] )

Table:

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.