how to join two fact tables in direct query with multiple common column

[Anonymous]

I have two fact tables one from operations dept and the other one from finance dept. Both the tables are from two different sql server and i am trying to work on data in DIRECT QUERY mode. 

Sample data below: Table1- MedrecNum and Table-2 MRN are same and is unique for every patient basically its the unique patient identity. MRN/medrecnum can be repeated in the table depending on number of visits by that individual. Count and CountD are different.

Table1-patient no and table-2 Encounter number are same and are not repeated. basically its different each time the same patient visits. it is specific to each visit. So count and countD would be same here.

1. What is the best way to merge these two tables or create connection given there are multiple common columns?

2. i want to find out patient nos in 2022

3. i want to find which MRN/MedrecNum note was completed? (note completed in second table is a binary column)

Currently i have created a direct relationship between the two tables by connecting them on patientno and encounter no (many to one/ table1-table2). When i try to slice the MRN (table2)by discharge year (table 1) i don't get the sliced value. its showing me error. 

When I try to filter MedRecNum(table1) by note completed(table-2) or consultation done (table-2), it doesn't get filtered.

My guess is that i have to create multiple dimension tables but what kind of and how do i do it in direct query? I believe the numbers are absurd because i have not created the relationship betweent he tables right. Please advise.

[hita]

Hi 

Is it possible to use the Merge function in the Power Query Editor? If not, it is recommended to change the mode to import in Powerbi Desktop.

[hita]

Hi 

Is it possible to use the Merge function in the Power Query Editor? https://www.acuitytraining.co.uk/news-tips/power-bi-merge-queries-append-queries/

If this is not possible, it is recommended to change the mode to import