Forum Discussion

MarcUrdang's avatar
MarcUrdang
Post Patron
6 years ago

Releated function

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

Need Help

5 Replies

  • armely's avatar
    armely
    Helper I
    6 years ago
    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's avatar
      MarcUrdang
      Post Patron
      6 years ago

      Hi Armely

       

      I tried that 🙂

       

      CalcName = Related('TableName'[FieldName])

       

      Is above correct syntax?

      Thanks

      Marc

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    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's avatar
      MarcUrdang
      Post Patron
      6 years ago

      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's avatar
        v-lili6-msft
        Community Support
        6 years ago

        hi  MarcUrdang 

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

         

        Regards,

        Lin