Create dimension table from multiple fact tables

[John_YZ]

Hi

I'm trying to create a dimension table for ID and Description from two different fact table.

TABLE1 and TABLE2 have several columns but I want to use only two, then remove duplicates for ID code.

When I use only CODE column it works fine, but when I try to bring the second column it duplicates one of ID rows.

I think it happens because the description/name is not exactly the same. For me it does not matter which one comes, since it brings one.

This is the code I'm using:

DISTINCT ( UNION( SUMMARIZE(TABLE1[CODE];TABLE1[DESCRIPTION]);SUMMARIZE(TABLE2[CODE];TABLE2[NAME])) )

I also tryied do make some kind of inner join but it yet duplicates:

IDTable = NATURALINNERJOIN ( 
                            DISTINCT(
                                UNION(
                                        DISTINCT(TABLE1[CODE]); 
                                        DISTINCT(TABLE2[CODE])
                                        )
                             ); 
                                UNION(
                                        SUMMARIZE(TABLE1[CODE];TABLE1[DESCRIPTION]);
                                        SUMMARIZE(TABLE2[CODE];TABLE2[NAME]) 

)
)

When I test it with one column only it does not duplicate the ID (same segment of code I try the inner join):

DISTINCT(UNION(DISTINCT(TABLE1[CODE]);DISTINCT(TABLE2[CODE]))

Anybody could help with this issue?

[Ashish_Mathur]

Hi,

Show your datasets and the expected result.

[Greg_Deckler · Accepted solution]

I think you are using DAX when you should probably be  using M (Power Query). Essentially, what I would suggest would be to create two new queries that pull from the exact same data sources but only includes the two rows you care about. Then, create an Append query that appends these together. Then, in this Append query, do a remove duplicates on your ID column. Disable data load and refresh on your two queries that are part of your Append query but leave data load and refresh enabled for your Append query. You should then have what you want, a table with distinct ID's.