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.
I have a table that I'll call Event.
I have another table called Biography.
Every Event row should be associated with one and only one Biography entry (but a single person_id may have multiple Biography entries associated with it, i.e., George Washington: The Early Years, and George_Washington: The Later Years can be separate Biography entries, but they must cover non-overlapping years). I want to add a calculated column to the Event table that will hold the title of the Biography entry to which it corresponds by checking that the person_id is equal and that the Event date falls between the start_date and end_date of the Biography entry.
How do I do this? I've tried a number of formulas but can't seem to get it quite right.
Solved! Go to Solution.
Hi,
Try this calculated column in the Events Table
=CALCULATE(FIRSTNONBLANK(Biogaphy[title],1),FILTER(Biography,Biography[start_date]<=EARLIER(date)&&Biography[end_date]>=EARLIER(date)&&Biography[person_id]=EARLIER(person_id)))
If this formula does not work, then share the link from where i can download your PBI file.
Hi,
Try this calculated column in the Events Table
=CALCULATE(FIRSTNONBLANK(Biogaphy[title],1),FILTER(Biography,Biography[start_date]<=EARLIER(date)&&Biography[end_date]>=EARLIER(date)&&Biography[person_id]=EARLIER(person_id)))
If this formula does not work, then share the link from where i can download your PBI file.
That worked. Thank you! I guess the EARLIER function was what I needed.
You are welcome.
User | Count |
---|---|
85 | |
84 | |
68 | |
65 | |
55 |
User | Count |
---|---|
126 | |
102 | |
90 | |
84 | |
66 |