Forum Discussion

limewire's avatar
limewire
Helper I
7 years ago
Solved

Relationship problem after appended data

Greetings~   Current situation: I had a nice relationship going between my query and 2 reference queries where I filtered for variables in the same column. This was so that I could create a calulat...
Need Help
Tutorial Requests
  • parry2k's avatar
    parry2k
    7 years ago

    limewire for everyone else, we added unique key in both the tables by concatenating year & program name columns and relationship become 1 to many and every existing calculation worked as expected. Cheers!!!

     

     

     

vanessafvg's avatar
vanessafvg
Community Champion
7 years ago

limewire  

 

what error are you getting, what problem is this creating?  What did the relationship do previously, can you post some data?  or the relationship view and tell me what you are expecting.

  • limewire's avatar
    limewire
    Helper I
    7 years ago

    vanessafvg  Thank you for replying!

     

    The error that I am experiencing is in my calculated column. I used this formula before

    Column_All = DIVIDE('1_All (2)'[All],RELATED('1_All (3)'[All]))*100

    -Queries 1_All (2) & 1_All (3) are both referenced from 1_All

    -I had a relationship of one to many set between 1_All (2) & 1_All (3).

    -1_All_ (3) had unique values (until I added data from previous years)

    -After I appended the data, I had to change the relationship between the two tables to many-to-many and now my formula won't work. It says that it can't find a related value from the 1_All(3) anymore :( 

     

    I really want the calculated column to work again! But none of them work now. 

     

    this is what 1_All(2) looks like - it has the year column in it as well. 

     

     

    this is what All_1(3) looks like. Previously, it had the unique values that made my calucated column formula work

     

    how my calculated columns look now

     

    • limewire's avatar
      limewire
      Helper I
      7 years ago

      Also I have tried to make a "key" table that only has programs (which is a unique value i believe) from the original query and made it a list but still i could not create a one-to-many relationship and thus could not have a calculated column. 

       

      I am stumped. Again!

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        7 years ago

        hi, limewire 

        When you append the data, for primary key, it will have multiple rows of data. (the relationship between the two tables is many-to-many).

        Now for this formula

        Column_All = DIVIDE('1_All (2)'[All],RELATED('1_All (3)'[All]))*100

        You need to add aconditional for aggregate calculation, not only use RELATED('1_All (3)'[All]).

        Try this formula:

        Column_All = DIVIDE('1_All (2)'[All], CALCULATE( MAX/MIN/SUM('1_All (3)'[All]) ) )*100

        or

        Column_All = DIVIDE('1_All (2)'[All], CALCULATE( MAX/MIN/SUM('1_All (3)'[All]), 

        FILTER(RELATEDTABLE('1_All (3)'),'1_All (3)'[Year]=MAX('1_All (3)'[Year]) ) )  )*100
        (This formula is search data for the maximum year)
         

        Best Regards,

        Lin

    • vanessafvg's avatar
      vanessafvg
      Community Champion
      7 years ago

      limewire  is there any chance you can share the data? or post a picture of relationship with the fulll structures of the tables, its probably going to be easier if you share the data

      • limewire's avatar
        limewire
        Helper I
        7 years ago

        vanessafvg 

        I can't share the data unfortunately but I can do my best with pictures!  

        CCEOI and Net Views have the same columns but differ in terms of category. My goal is to create a calculated column using CCEOI values as the numerator and Net Views values as the denominator.  I was able to this with a calucated column before I appended the data. How can I do it now? 

        relationship table ~ renamed the tables to make it easier