Solved: Table Relationship Doesn't Work. - Page 2

Anonymous · ‎12-31-2018

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 · ‎01-04-2019

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

View solution in original post

Table Relationship Doesn't Work.

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly