Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
95 | |
77 | |
72 | |
65 |
User | Count |
---|---|
136 | |
109 | |
104 | |
82 | |
73 |