Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all!
I am having some trouble with the logic behind how to solve this.
I have two tables where I am tring to match records based on ticket numbers, but due to typos and other things there are records that do not have matching ticket numbers. However, the Prop ID will match exactly, and ticket date on the first table will always be one day before the ticket date on the second table.
Example Table 1:
Ticket Number | Ticket Date | Prop ID |
555821 | 11/11/22 | WW555 |
101991 | 10/20/22 | WW001 |
808554 | 12/12/22 | WW333 |
Example Table 2:
Ticket Number | Ticket Date | Prop ID |
5421 | 11/12/22 | WW555 |
190 | 7/1/22 | WW001 |
800500 | 12/13/22 | WW333 |
In the two examples above, the first and third records would link because the prop ID matches and the date is 1 day apart. The middle row would not link because the date is off.
Can anyone help with what the syntax for this might be? It would be fine if it either created a column that said "Match" or "No match" beside the records, or if it only returned records with a match. Really just any help getting it filtered would be great. Thank you!
Solved! Go to Solution.
Hi @powerbiqu
You can create a calculated column in Table2 as follows
Matching Ticket =
MAXX (
FILTER (
Table1,
Table1[Prop ID] = Table2[Prop ID]
&& Table1[Ticket Date] = Table2[Ticket Date] - 1
),
Table1[Ticket Number]
)
Hi @powerbiqu
You can create a calculated column in Table2 as follows
Matching Ticket =
MAXX (
FILTER (
Table1,
Table1[Prop ID] = Table2[Prop ID]
&& Table1[Ticket Date] = Table2[Ticket Date] - 1
),
Table1[Ticket Number]
)