Connecting similar table with different content

[adnan_makie]

Hi Everyone, 

I have 2 tables that have duplicated fields (Service ID and Material ID). These tables are similar tables (Table 1 and Table 2) but each one coming from a different system so there might be missing ids, quanitites, different statuses, etc.

I created a composite key in each of these tables to connect them through a bridge table that contains unique ids from both tables that are not duplicated. In that way, I can know which ids exists in Table 1 but not in 2 so result for filter is <Blank>

The filter for ids works fine, is it also safe to filter statuses in Table 1 that are "Used" and Check which Ids are "Not Used" in Table 2 or should I create a dimension Table for statuses. Same applies for quantities if I want to compare quantities.

Result: Shall I create other dimension tables for fields I want to compare or a bridge table with ids is sufficient? (For instance, filtering the common used ids existing in 1 but not in 2 or the used ids in 1 that are not used in 2)

[Anonymous]

Hi, @adnan_makie 

Consider the diversity and complexity of the states in the table. If the states are few and consistent, creating a dimension table for the states can simplify the model and analysis.

If you need to adjust or standardize, consider implementing calculated columns or measures to facilitate these comparisons. Make sure that the bridge table effectively supports relationships between tables, especially for unique ID comparisons. If the analysis becomes more complex, reassess whether additional bridging tables or dimension tables are needed to maintain model performance and clarity. For further guidance on creating and managing BI semantic models, you can check out the following links:

BI solution architecture in the Center of Excellence - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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