Related function

[MarcUrdang]

Hello

I am trying to write a calculation where I compare various data and then if true return a result. One of these fields comes from another table and when I use the Related function it returns an error saying that either the filed doesn't exist or there is no relationship bestween the tables (which there is).

Any guidance would be appreciated.

The calc is as follows:

Client Order Number =

IF([Charge Code/ GL] = "DRB","Drawback",

             IF('Chg Code Master'[Charge Code/ GL] <> "DRB" && RELATED('Invoice Line'[ClientLineOrderNo]<>BLANK(),                                               RELATED('Invoice Line'[ClientLineOrderNo],"PO Needed"))))

[v-frfei-msft]

Hi @MarcUrdang ,

Kindly share your sample data and excepted result to me, You can just copy and paste your content here directly. 

[amitchandak]

@MarcUrdang 

First, both tables should be related. Second I see two related. I think you have to use it only on one side. Side from where you are trying to get data.

There is detailed explanation on https://databear.com/dax-related-table-functions/

[MarcUrdang]

Hi Amit .. yes they are both related .. and the tables are many to many relationship .. so I guess then i cannot do this?

[amitchandak]

@MarcUrdang , need sample to test. I think in your formula, you used related on both sides. try using only on one side.

Some time I also do this

Get from Table 2 in table 1

New Column = maxx(filter(table2,table2[Col1]= table1[col1] && table2[Col2]= table1[col2] ),table2[required_col])

[MarcUrdang]

Hi Vivek .. thx so much .. it is very close but not quite correct as now it brings back just one of the PO Numbers against every line nad not the actual PO number for that line .. can I mail you the workbook? You will then see what I mean. Or perhaps I can show you in Teams? I can set up a call?

thanks
Marc

[MarcUrdang]

Sorry Amit .. I put the wrong name in 😞 

[amitchandak]

@ImkeF , @Greg_Deckler , Can you help.

These tables have many to many join on the job no. And one on the table has line no. In visual he wants to bring line no from another table.

[vivran22 · Accepted solution]

Hello @MarcUrdang 

Can you share the sample pbix file or snapshot of the model view?

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

[MarcUrdang]

Hi Vivek .. thank you so much for your quick reply .. our company doesn't allow us to post but I am able to email the .pbix if you don't mind that? If you can supply an email address I will send immediately.

thanks
Marc