Forum Discussion
Table Relationship Doesn't Work.
Hello Communities,
I am converting excel report into Power Bi Report.
I am getting below error” The column 'Accessories Historical Data[ACCESSORIES HISTORICAL UI A]' either doesn't exist or doesn't have a relationship to any table available in the current context.”
Current 2017 AG = IFERROR(CALCULATE(AVERAGE('Accessories Historical Data'[HISTORICAL 2017 Duplicate with Calculation]),
FILTER('01-Accessories','01-Accessories'[Current UI A]=RELATED('Accessories Historical Data'[ACCESSORIES HISTORICAL UI A]))),0000)
Datatype
HISTORICAL 2017 Duplicate with Calculation=Numeric
Current UI A, ACCESSORIES HISTORICAL UI A=Alphanumeric
***Both tables 'Accessories Historical Data’ & '01-Accessories' are connected with many to many relationship and both direcetional.
Hi Anonymous
In fact, many to many relationship works.
In you scenario, why it doesn't work is that:
For example, in my test example,
there are two "a" values in table 1, also two "a" values in table2,
then use "RELATED" function in table1 to get "a" values from table2,
it can't determine which "a" to return.
For this scenario, you could follow my method(use aggregation) in my last post, it is a general way to solve this kind problems.
Regrading using tables with many to many relationship in power bi, you could refer to this article
https://www.seerinteractive.com/blog/join-many-many-power-bi/
Best Regards
Maggie
11 Replies
Based on the error:
"The column 'Accessories Historical Data[ACCESSORIES HISTORICAL UI A]' either doesn't exist or doesn't have a relationship to any table available in the current context.”
it is pretty much what it says. Either the column is renamed or deleted, or the columns in the calculation do not have a relationship defined between the tables they reside in.
<Nirvana>it is pretty much what it says. Either the column is renamed or deleted, or the columns in the calculation do not have a relationship defined between the tables they reside in.
>I did check that column is available and never ever renamed or delted.
>table does have relationship and when I typed formula intelisense shows me column name (where I marked it in yellow colour) and when I type formula intelisense does not show me column name(where I marked it in red colour)
Current 2017 AG = IFERROR(CALCULATE(AVERAGE('Accessories Historical Data'[HISTORICAL 2017 Duplicate with Calculation]),
FILTER('01-Accessories','01-Accessories'[Current UI A]=RELATED('Accessories Historical Data'[ACCESSORIES HISTORICAL UI A]))),0000)
Hi Anonymous
Please take nirvana_moksh's suggestion into consideration.
To find what's wrong, you could try to split the formula in different measures and columns.
Write these in the table where “Current 2017 AG” is written.
measure1=AVERAGE('Accessories Historical Data'[HISTORICAL 2017 Duplicate with Calculation])
measure2=
CALCULATE(AVERAGE('Accessories Historical Data'[HISTORICAL 2017 Duplicate with Calculation]),
FILTER('01-Accessories','01-Accessories'[Current UI A]="enter something match the cells here"))
column1=RELATED('Accessories Historical Data'[ACCESSORIES HISTORICAL UI A])
If you have problem finding the reason for errors by yourself, please let me know and share some example data for better analysis.
Best Regards
Maggie
Hi Maggie,
Please see below my requirements,
Current 2017 AG = IFERROR(CALCULATE(AVERAGE('Accessories Historical Data'[HISTORICAL 2017 Duplicate with Calculation]),
FILTER('01-Accessories','01-Accessories'[Current UI A]=RELATED('Accessories Historical Data'[ACCESSORIES HISTORICAL UI A]))),"not available")
<Maggie>measure1=AVERAGE('Accessories Historical Data'[HISTORICAL 2017 Duplicate with Calculation])
<power bi>it is working fine
measure2=
CALCULATE(AVERAGE('Accessories Historical Data'[HISTORICAL 2017 Duplicate with Calculation]),
FILTER('01-Accessories','01-Accessories'[Current UI A]="enter something match the cells here"))
<power bi>it is working fine
column1=RELATED('Accessories Historical Data'[ACCESSORIES HISTORICAL UI A])
<powerbi> I got below error,when I typed related intelisense showed me the column name.
“The column 'Accessories Historical Data[ACCESSORIES HISTORICAL UI A]' either doesn't exist or doesn't have a relationship to any table available in the current context.”
thank you
Hi Anonymous
As tested, when there is no relationship between two tables, i can use column inside function "AVERAGE" in another table in this table
Which relationship you create? could you show me a screenshot?
Are there many duplicates for each value in "current ui a" or "accessories historical ui a" column?
Best Regards
Maggie
Hi Anonymous
Using the many-many relationship, i can reproduce the problem as yours.
To avoid this error and get the average, i work as below
add columns from two tables (which relationship is many to many)
Then
Best Regards
Maggie
Hi Anonymous
In fact, many to many relationship works.
In you scenario, why it doesn't work is that:
For example, in my test example,
there are two "a" values in table 1, also two "a" values in table2,
then use "RELATED" function in table1 to get "a" values from table2,
it can't determine which "a" to return.
For this scenario, you could follow my method(use aggregation) in my last post, it is a general way to solve this kind problems.
Regrading using tables with many to many relationship in power bi, you could refer to this article
https://www.seerinteractive.com/blog/join-many-many-power-bi/
Best Regards
Maggie
