Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
MarcUrdang
Post Patron
Post Patron

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

5 REPLIES 5
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

hi  @MarcUrdang 

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

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
armely
Helper I
Helper I

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.

Hi Armely

 

I tried that 🙂

 

CalcName = Related('TableName'[FieldName])

 

Is above correct syntax?

Thanks

Marc

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.