Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-juanli-msft
      Community Support
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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,