Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Match 2 fact tables best method DAX or Query editor

HI all,

 

I have 2 the same fact tables coming from different sources (API vs datawarehouse). Now I want to set up an auditing dashboard and check for differences between the two tables.

 

Specifically I want the rows returned that are present in the API but not in the datawarehouse. 

 

I have a Dim table connected to the 2 tables which enables me to calculate the overal difference in rows but cannot return only the rows that are not present in the datawarehouse. 

 

I think I have 2 options: 

1. Create a third fact table by reference one fact table in query editor which enables me to link the 2 id fields and this could give me the rows that are not matched. 
2. Some DAX measure which creates a virtual relationship. 

I was hoping to fix this witch dax so I hope you can help me. 

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, Anonymous 

    If you want to create a virtual relationship for the two fact tables using DAX in Power BI, you can try to use the NATURALINNERJOIN and NATURALLEFTOUTERJOIN function. Here’s a document that explains this in detail:

    https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

    In my opinion, I prefer to suggest you to follow MattAllington’s suggestion to finish the table merge operations in the Power query.

     

    Best Regards,

    Community Support Team _Robert Qin

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

2 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    5 years ago

    I would do this in power query. Merge on a primary key for the fact tables (you may need a composite key) and select an anti-join. Rows that appear in one table and not the other.

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

    If you want to create a virtual relationship for the two fact tables using DAX in Power BI, you can try to use the NATURALINNERJOIN and NATURALLEFTOUTERJOIN function. Here’s a document that explains this in detail:

    https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

    In my opinion, I prefer to suggest you to follow MattAllington’s suggestion to finish the table merge operations in the Power query.

     

    Best Regards,

    Community Support Team _Robert Qin

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