Table Relationship Doesn't Work.

[Anonymous]

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.

[v-juanli-msft]

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 

[Anonymous]

Hi Maggie,

Wow what an innovative idea.

Thank you so much for your solutions,still i am confuse many to many relationship is not working.

Thanks

[v-juanli-msft · Accepted solution]

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

[Anonymous]

Happy Friday and Thank yo so much Maggie for detail explaniations and Link is also very informative.

[v-juanli-msft]

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

[Anonymous]

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

[v-juanli-msft]

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

[Anonymous]

Hi Meggie,

Do you suggest lookup with if condtions?

Thanks

Tejas

[Anonymous]

Hi Maggie,

Are there many duplicates for each value in "current ui a" or "accessories historical ui a" column?

Yes ,there are duplicates and I am converting excel report into power bi and excel has two tabsheet one for current and one for historical,both tables has common column UI .I have created many to  many and both directional on accessories UI and Historical UI.

<Maggie>Which relationship you create? could you show me a screenshot?

I have created relationship on both tables using many to many and both directional.Please see below,

[nirvana_moksh]

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.

[Anonymous]

<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)