RELATED doesn't work for One-to-Many

[Anonymous]

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"

[Stachu]

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?

[Anonymous]

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. 

[v-lili6-msft]

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

[Anonymous]

Thanks, @v-lili6-msft . You're right, one of the table is imported while the another one is direct query. I did tried on "LOOKUPVALUE" as well actually but it turns out with this error "A calculated column or RLS expression on a DirectQuery table cannot reference tables from a different data source." 

It looks like there are no way to resolve this if we were to maintain one of the table as "DirectQuery". 

[v-lili6-msft]

hi, @Anonymous 

In my side, It works well. and if you could try this formula:

Column = CALCULATE(SUM('Comissions&Margin'[Comissions]),FILTER('Comissions&Margin','Comissions&Margin'[SalesCode]='Table'[SalesCode]))

Best Regards,

Lin

[Anonymous]

Thanks, @v-lili6-msft , this could be helpful but I'm still clueless why it doesn't work for direct query for my case. 

---

@v-lili6-msft wrote:

hi, @Anonymous 

In my side, It works well. and if you could try this formula:

Column = CALCULATE(SUM('Comissions&Margin'[Comissions]),FILTER('Comissions&Margin','Comissions&Margin'[SalesCode]='Table'[SalesCode]))

Best Regards,

Lin

---

[v-lili6-msft]

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.  

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

[Anonymous · Accepted solution]

@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.  

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. 

[v-lili6-msft]

HI, @Anonymous 

Great!  Could you please accept the reply as solution, that way, other community members will easily find the solution when they get same issue.  

Best Regards,

Lin