cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft
Microsoft

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors