Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jansenuy
Helper I
Helper I

DOUBLE VLOOKUP/TWO RELATIONSHIPS

Hi All,

 

I hope you guys can help me with shaping my databases for PowerBI. I have two tables with me that both have a supposedly common identifier. The first table are schedules and then the second table are rows of information (e.g. clients of the day) pertaining to a certain schedule. The identifier is supposed to be constant and matched accross both tables.

 

Part of my long identifier (REGISTERCODE-DATE-LOCATION) is a date that is supposed to be unique and properly matching with the customer database. However, there are some server bugs that uses the day after posting date which makes it unmatched with the second database.

 

I already made two identifiers for the first (schedule database) the first one is the original one and the second one with the date that is the day after.

 

I need to find a way to create a third column of identifier that completely matches with the second database.

 

I tried the following:

1. Try using the relationships but failed because only one can be active at a time.

2. Try using the nested lookup function to a calculated column:

Final Identifier = IF(OR((ISERROR(LOOKUPVALUE('Tasks Export'[Identifier],
'Tasks Export'[Identifier - Copy],Schedules[Proforma Identifier]))),
(ISBLANK(LOOKUPVALUE('Tasks Export'[Identifier],
'Tasks Export'[Identifier - Copy],Schedules[Proforma Identifier])))),
LOOKUPVALUE('Tasks Export'[Identifier],'Tasks Export'[Identifier - Copy],
Schedules[Actual Identifier]),LOOKUPVALUE('Tasks Export'[Identifier],
'Tasks Export'[Identifier - Copy],Schedules[Proforma Identifier]))

 

3. The real purpose of me connecting the databases is to associate client transactions (2nd database) to assigned persons and schedules (1st person). I tried using merge queries but also failed as I tried all kinds of joins but failed to obtain a column that merges the identifiers.

 

Hope you guys can advise me on what to do. I tried to be as clear as possible. Many thanks.

1 REPLY 1
v-huizhn-msft
Employee
Employee

Hi @jansenuy,

After research, I can't reproduce your scenario without sample table. Please share your sample table, you can create some fake data whose structures are same with yours. Then post the .pbix file, so that we can share solution which is close to your requirement.

Best Regards,
Angelia

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.