Wrapping Distinct around a union

[mflorian]

I have created three summary tables that pulls revenue information from three disparate systems for a consolidated report. Two of the tables have the potential for duplicate data due to a data classification problem that is currently being worked on. In the meantime, we need to create a data set for the user to report from.

The union statement works fine but it returns duplicate rows when the two systems have the same data. I have set all data fields for the three summaries to be the same in SSAS. When I wrap a distinct around the union, I get the same results as if the union was not there.

Here is the DAX:

=DISTINCT(UNION('Table A, 'Table B', 'Table C'))

Is there a better way to accomplish this? It seems straight forward but ... nothing ever is.

[Ashish_Mathur · Accepted solution]

Hi,

You may append data using Query Editor and then select all column > Right click > Remove Duplicates > Close and Load.

[pobaldi]

If I do remove duplicates that way, and then refresh all my union'ed data sources and add rows, etc. will the duplicates be removed on the refreshed data source as well?

[Ashish_Mathur]

Yes