Lookup values in tables based on columns which do not define the relationship

[michellepace]

Hi there,

I have these two tables with a 1-1 relationship defined on AccNumber. What I need to do is populate the MainAccDescrip column.

-------------------------------------------------------

AllAccounts

AccNumber | AccDescrip

pnlAccounts

AccNumber | MainAccNumber | MainAccDescrip |

-------------------------------------------------------

What I need to do is populate the MainAccDescrip column. That is, get the 'AccDescrip' value from the AllAccounts table using the value in MainAccNumber. If I had to write pseudo 'matrix' code, it would look like this:

MainAccDescrip = AllAccounts[MainAccNumber].AccDescrip

Can anyone shed some light on how I do this please?

[Ashish_Mathur]

Hi,

Assuming all entries in the AccNumber column of AllAccounts Table are unique and there are no blanks in this column, build a relationship from the AccNumber of PnlAccounts Table to the AccNumber column of the AllAccounts Table.  In the PnlAccounts Table, write this calculated column formula

=RELATED(AllAccounts[AccDescrip])

Hope this helps.

[michellepace]

Thanks for trying but my question is perhaps unclear. I need to use MainAccNumber as the lookup in the AccNumber column in the top table. What you have suggested won't work for me. Thanks though for replying 🙂

[Ashish_Mathur]

You have managed to completely confuse me.  Share the link from where i can download your PBI file and clearly show the expected result there.

[michellepace]

I need to create the two columns MainGlAccDesc and SubGLAccDesc. AccNumber is a unique field in the AllAccounts table, pnlAccounts table is a subset of the AllAccounts table.

PowerBi file here:  https://michellepace.com/?dir=PowerBi

[Ashish_Mathur]

Hi,

There is no AllAccounts Table in the PBI file.

[Ashish_Mathur]

Check your other post.  Please do not post the same question more than once.

[michellepace]

Hi Ashsih, thank you for your replies. This is not at all the same question. Please take a read in detail. Sorry I'm just a completely confused newbie here struggling. A lot.