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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Tricky Relationship Situation

I have 2 data sources connected through a unique ID. One is a SQL data source, the other is from Salesforce. I have a one-to-many relationship between the 2 tables:

1.PNG

 

So, 'Unique ID' from Delta account = 'Co' from M3 Billing. This works fine for the most part, but there is some data from my M3 Billing table that didn't link up correctly due to the unique ID being different between the data sources. So any company that starts with a 'F' has blank fields for everything in a table I have. The "Co" column is coming from my M3 Billing dataset;

1.PNG

 

So what's happening, is for some reason these companies have a different 'Unique ID' field in Salesforce:

1.PNG

 

So for these particular set of records, I need it to be linked up to the 'Flex M3 ID' field rather than the 'Unique ID' field so I can get my data populated the way I need to. It's tricky because most records are fine and give me what I need when linking 'Unique ID'->'Co' from both tables, but I don't know how to account for the remaining records that need the relationship to be 'Unique ID'->'Flex M3 ID' so all the data gets populated. Is this possible?

 

 

3 REPLIES 3
danextian
Super User
Super User

Hi @Anonymous

 

I guess what @Vvelarde was trying to say was to create a calculated column using a formula like below and use this column to connect the two tables assuming that you are referring to Company as Delta Account. 

 

New Unique ID =
IF (
    LEFT ( Delta[Delta Account], 1 ) = "F",
    Delta[Flex M3 ID],
    Delta[Unique ID]
)









Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Vvelarde
Community Champion
Community Champion

@Anonymous

 

I will try to created a calculated column in both tables with this condition.

 

New Column = Unique ID Column except That Co Start with F. If Start with F use Flex M3 ID.

 

And use this new column to related.

 

Victor

 

 




Lima - Peru
Anonymous
Not applicable

Hi @Vvelarde, this makes perfect sense but I'm not exactly sure what that formula would look like. The Flex M3 ID only exists in one of my datasets.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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