How to link Table A to Table B - Vlookup?

[AColeman]

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.

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

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

So i want to be able to achieve the following:

Any assistance will be greatly appreciated

[MalS]

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]

@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.

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?

[MalS]

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]

@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.....

[MalS]

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...