Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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.
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
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
Hi Armely
I tried that 🙂
CalcName = Related('TableName'[FieldName])
Is above correct syntax?
Thanks
Marc
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
107 | |
99 | |
91 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |