Releated function

[MarcUrdang]

Hi ... I would like some advice regarding using the Related funciton.

I have two data sources joined by a created field in the one and a supplied field in the other. The first is my main data source where there is a field called Master Bill. This field is AlphaNumeric. The second data source carries data pertaining to this particular column in the first.  for example:

Main data source = ConsolInbound[MasterBill] ..... example of a Master Bill is:    ABCD1111111 or 3031111111  ie could be AlphaNumberic or just Numeric

Secondary data source = CarrierInbound[3DigitCode} ... example of a 3 digit code is:   303

I need to be able to use the MasterBill in my main table in the following way. I created another column in my main data source called 3DigitCodeCHK which takes the first 3 char from the field in the ConsolInbound[MasterBill] field and then joined the two tables using this new field and the original field in the secondary table.  ie 3DigitCodeCHK linked to 3DigitCode

I then want to say: IF (related('ConsolInbound'[3DigitCodeCHK] = 'CarrierInbound'[3DigitCode] then insert data from another field from the Carrier Table otherwise insert data from Master Bill

However I get this error: The column 'IATA Airlines[3-Digit Code]' either doesn't exist or doesn't have a relationship to any table available in the current context.

The tables seem to be joined correctly. The second data source only has one instance of each while the main has many .. however the only join allowed is a many to many relationship.

Any help will be much appreciated.

thanks

[v-lili6-msft]

hi @MarcUrdang 

Since your model is many to many relationship, however the only join allowed is a many to many relationship.

So you could not use Releated function in many to many relationship.

Releated function is only work in one to one relationship or one to many relationship.

Since for table which is many, it has multiple value, releated only return one value, so it couldn't achieve.

For your case, you could use this formula

CalcName = MAXX ( RELATEDTABLE ( 'TableName' ), 'TableName'[FieldName] )

and here is a simple sample pbix file, you could try it and you will understand it well.

Regards,

Lin

[MarcUrdang]

Hi again .. no there is still something wrong ... Can we try and Skype this? If not I will try and get someone else to help ... I need to fix this by tomorrow morning.

thanks
Marc

[v-lili6-msft]

hi  @MarcUrdang 

If possible, could you please share your sample pbix file and your expected output, that will be a great help.

Regards,

Lin

[armely]

How about you make a column in your master table that contains data from your secondary table using related function. Now with the new created column use your IF condition for comparison.

[MarcUrdang]

Hi Armely

I tried that 🙂

CalcName = Related('TableName'[FieldName])

Is above correct syntax?

Thanks

Marc