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
AColeman
Frequent Visitor

How to link Table A to Table B - Vlookup?

 

Hi Guys

 

Im pretty new to PowerBi so im rely relying on these forums alot.

I have the following DataModel in PowerBI but am struggling to achieve the following:

 

I have the following table: AccountStructure

It contains the following 2 calculations.

AccountStructure.png

 

Then i also Have a table caled CostCentreStructure which contains the CCLevel2 calculation

CostCentreStructure.png

Now this together i created in accountstructure the calculation CCACC which is a unique key which will link to a table i have 

called Comments as per above with a calculated column called CommLink

So CommLink will link back to CCACC to provide me with the comments details.

 

My table linking structure looks as follow,but ofcourse i cant link my Comments Table to my AccountStructure table

TableLinks.png

 

So i want to be able to achieve the following:

Visualization.png

 

Any assistance will be greatly appreciated

 

 

 

 

 

 

 

5 REPLIES 5
MalS
Resolver III
Resolver III

I don't quite follow all the calcs, but I assume the problem is that you can't link AccountStructures[CCACC]  to Comments[CommLink] because that is a many to many relationship?

 

If so, you need another table that contains only unique values of your key field. Then you can create a relationship from both AccountStructures[CCACC]  and Comments[CommLink] to this other table. 

 

If you don't have a table of unique values, you can create one. Assuming one of your existing tables contains every possible value of the key field (even if some values occur multiple times in the table) you can:

 

1. Edit queries

2. Right click on the query that contains every possible value of the key field, and select Reference

3. Right click on the key field column and Remove Other Columns

4. Right click again and select Remove Duplicates

 

(If there is no single table with every possible value, you can append several queries together first, then follow the steps above)

AColeman
Frequent Visitor

@MalS

 

Thanks alot for the reply.

The problem is not that there is a many to many relationship, i can't even establish the link between CommLink which is a unique id for per year, period.

It just show me this little icon when i drag to make the connection.

Link.png

 

Dont know if it might be because if i view the detail of these 2 tables in question i cant actually see the column value of CCACC?

column.png

Oh, it looks like CCACC is a measure. 

 

Try adding it as a New Column instead (click Edit Queries > Add Column > Custom Column, and enter your formula there). Then see if you can make the connection.

 

AColeman
Frequent Visitor

@MalS, nope didnt work and i think its a bit more complicated, as this solution i imported from PowerPivot into PowerBI.

 

CCACC = [AccCom]&[CCLevel2]

 

Now AccCom is from Table AccountStructures

 

AccCom = IF (AND(Actuals[Actuals]=0,Budgets[Budgets]=0),BLANK(),IF ([BrowseDepth]=2, CALCULATE(min([Acc_Level11]),filter('Accounts',[AccRows]>0)),
IF ([BrowseDepth]=3,CALCULATE(min([Acc_Level12]),filter('Accounts',[AccRows]>0))))) 

 

Now CCLevel2 is from Table CostCentreStructure

 

CCLevel2 = IF (AND(Actuals[Actuals]=0,Budgets[Budgets]=0),BLANK(),CALCULATE(min([CostCentre_Level10]),filter('CostCentres',[ccRows]>0)))

 

But when i copy CCACC to a new column it only returns AccCom values and nothing for CCLevel2

 

even if i try changing it to CONCATENATE AccountStructures[AccCom],CostCentreStructures[CCLevel2]

 

But when i drag CCLevel2 into a grid visualization i see the data.....

Hmm - not really sure why you would see values from AccCom but not CCLevel2. It's a bit diffcult to figure out without the .pbix file to play around with. 

 

I would probably try adding AccCom and CCLevel2 as columns too, to see if that gives any clues. Otherwise, I think I am out of ideas...

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.