Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to Solution.
@v-lili6-msft, I have resolved this issue by changing the data connection to "Import"
@v-lili6-msft wrote:hi, @JOO13
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.
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, @JOO13
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
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".
hi, @JOO13
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
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, @JOO13
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
hi, @JOO13
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
@v-lili6-msft, I have resolved this issue by changing the data connection to "Import"
@v-lili6-msft wrote:hi, @JOO13
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.
HI, @JOO13
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |