Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello! I'm trying to create a new column that checks if certain information has a match in another table, but I'm failing because my reference column matches more than once.
I have an "Inspections Table", like this:
Month | Inspection Leader | Inspected Location |
September | Albert | Front yard |
September | Bernard | Back yard |
October | Cromwell | Front yard |
October | Bernard | Back yard |
December | Cromwell | West yard |
And a "Planning Table", like this:
Month | Planned Location | Planned Leader |
September | Front yard | Albert |
September | Back yard | Cromwell |
October | West yard | Cromwell |
October | Front yard | Bernard |
December | West yard | Dennis |
December | Back yard | Bernard |
I'm trying to create two columns at the end of the "Inspections Table" like these:
Month | Inspection Leader | Inspected Location | Same Leader as planned for the month? | Same Location as planned for the month? |
September | Albert | Front yard | Yes | Yes |
September | Bernard | Back yard | No | Yes |
October | Cromwell | Front yard | Yes | Yes |
October | Bernard | Back yard | Yes | No |
December | Cromwell | West yard | No | No |
I tried going for a FILTER with an IF in DAX, but I can't get it right. I think I'm having trouble with the fact that months may occur more than once in the "Planning Table".
Thanks in advance!
Solved! Go to Solution.
I can get everything to match up except the December - West Yard I must be missing something. It is in both your inspections and planning table, just with a different leader so I get a Yes for [Same Location as planned for the month?]. Anyway, here are the calculated columns using LOOKUPVALUE.
Same Leader as planned for the month? =
IF (
Inspections[Inspection Leader]
= LOOKUPVALUE (
Planning[Planned Leader],
Planning[Month], Inspections[Month],
Planning[Planned Leader], Inspections[Inspection Leader]
),
"Yes",
"No"
)
Same Location as planned for the month? =
IF (
Inspections[Inspected Location]
= LOOKUPVALUE (
Planning[Planned Location],
Planning[Month], Inspections[Month],
Planning[Planned Location],Inspections[Inspected Location]
),
"Yes",
"No"
)
Thank you @jdbuchanan71 ! Now I see where I messed up. Marked your reply as solution.
I can get everything to match up except the December - West Yard I must be missing something. It is in both your inspections and planning table, just with a different leader so I get a Yes for [Same Location as planned for the month?]. Anyway, here are the calculated columns using LOOKUPVALUE.
Same Leader as planned for the month? =
IF (
Inspections[Inspection Leader]
= LOOKUPVALUE (
Planning[Planned Leader],
Planning[Month], Inspections[Month],
Planning[Planned Leader], Inspections[Inspection Leader]
),
"Yes",
"No"
)
Same Location as planned for the month? =
IF (
Inspections[Inspected Location]
= LOOKUPVALUE (
Planning[Planned Location],
Planning[Month], Inspections[Month],
Planning[Planned Location],Inspections[Inspected Location]
),
"Yes",
"No"
)
User | Count |
---|---|
86 | |
82 | |
68 | |
66 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |