Forum Discussion
RELATED doesn't work for One-to-Many
Dear all,
I'm trying to lookup some values from anoter table but encountered with below error.
Dax used for the calculated column: Com = RELATED('Comissions&Margin'[Comissions])
Error Msg:
The column 'Comissions&Margin'[Comissions]' either doesn't have a relationship to any table available in the current context.
In fact, the relationships of both tables have been linked through "Sales Code", table Comissions&Margin- table Sales, one to many and has already set to "Active relationship" as well.
table Sales:
| Sales Code | Customers | Sales |
| A | C | 100 |
| A | D | 200 |
| B | A | 500 |
| B | C | 200 |
| B | D | 100 |
table Comissions&Margin
| Sales Code | Comissions | Target Margin |
| A | 0.1 | 0.3 |
| B | 0.03 | 0.5 |
desired output
| Sales Code | Customers | Sales | Com |
| A | C | 100 | 0.1 |
| A | D | 200 | 0.1 |
| B | A | 500 | 0.03 |
| B | C | 200 | 0.03 |
| B | D | 100 | 0.03 |
Any idea what is missing here/what are the alternatives? Thanks in advance!
Note*
- This is DirectQuery Mode, hence "RELATEDTABLE" could not be suitable
- Need "CALCULATED" column to be used for my case instead of "MEASURES"
v-lili6-msft, I have resolved this issue by changing the data connection to "Import"
v-lili6-msft wrote:hi, Anonymous
This issue had been was reported to the product group before. ICM: 100120097
PG team already created a bug item to track this issue.
PG team will try to add comments or a new article to clarify the related function work in certain scenarios.
you could use the solution I had provided above for a substitution for now. :smileyvery-happy:
And could you please tell me if you have other problem? If not, could you please mark the helpful replies as Answered?
Best Regards,
Lin
. Thank you.
9 Replies
based on what you posted it should work fine as you wrote it
can you post the screenshot of the relationships from the PowerBI? is it single direction or bidirectional? are there any other joins that may cause relationship ambiguity?
Hi, Stachu Thanks for reply! It is one direction from table Comissions&Margin to table Sales. There are no other tables linked to both of them either which is pretty straight forward. I saw other related posts mentioning "RELATED" is only USABLE for 1-1 relationship, this could be one of the reason that causes the error and hence I'm seeking for alternatives.
HI, Anonymous
Based on my research, for your case, it should be that one of the two table is import(or is created by new table) and another is directquery.
So just use this LOOKUPVALUE Function for it:
Column 2 = LOOKUPVALUE('Comissions&Margin'[Comissions],'Comissions&Margin'[SalesCode],'Table'[SalesCode])Then it should work well.
Best Regards,
Lin
