Trying to merge a measure table with a table

[Anonymous]

Ok, 

I have an absence table and a measure table with the date of the medical certificates that I can't merge in power query I think because it only exist in the Power BI.

Absence table

Name	IP	Date (mm/dd/yyyy)
April Day	1680749	12/27/2022
April Day	1680749	1/4/2023
April Day	1680749	1/5/2023
April Day	1680749	1/12/2023
April Day	1680749	1/13/2023
April Day	1680749	1/19/2023
April Day	1680749	1/27/2023
Edward Patterson	1877321	11/8/2022
Edward Patterson	1877321	11/9/2022
Edward Patterson	1877321	11/10/2022

Medical certificates measure table

Date	ID	Name
3/16/2023	2337266	Oscar
1/4/2023	1680749	April Day
1/5/2023	1680749	April Day
11/8/2022	1877321	Edward Patterson
11/9/2022	1877321	Edward Patterson
3/22/2023	2250748	Elena
3/23/2023	2250748	Elena

How do i merge this two tables in the way that a new column appears in the first table that indicates if we received a certificate for that absence. Like this:

Name	IP	Date (mm/dd/yyyy)	Certificate
April Day	1680749	12/27/2022
April Day	1680749	1/4/2023	Received
April Day	1680749	1/5/2023	Received
April Day	1680749	1/12/2023
April Day	1680749	1/13/2023
April Day	1680749	1/19/2023
April Day	1680749	1/27/2023
Edward Patterson	1877321	11/8/2022	Received
Edward Patterson	1877321	11/9/2022	Received
Edward Patterson	1877321	11/10/2022

[Ashish_Mathur]

Hi,

You will not be able to do that merge for the reason that you mentioend.  If you have a seperate physical Medical certificates Fact Table then there are multiple way to get to your desired result.

[lbendlin]

1. create a composite key (Name+Date) in both tables

2. join the tables in the data model with certificate as the dimension and Absence as fact

3. use RELATED in the absence table to pull in the certificate status (via proof of existence)