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

     

     

     

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

    • limewire's avatar
      limewire
      Helper I
      7 years ago

      v-lili6-msft Thank you so much for your reply! 

      I understand why the relationship is now many-to-many. Your solution made sense so I do not understand why the results are incorrect. 

       

      I renamed my tables (easier to understand for you and easier for me to explain!) where All_1(2) = CCEOI and All_1(3) = Net Views. I used both of your recommended calculations for the calculated column but I can see that the formula did not execute as well as it did before I appended my data.  I don't understand why both formulas do not work. I used the MAX for each one.

       

      the new one which has more years of datathis is the old one, before I appended data, note that i have a report slider that filters category that i plan to use on the new one once if we can get it to work :(

       

       

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

        hi, limewire 

        I have test on my side, it works well. Just from the screenshot, we couldn't find out the reason.

        Maybe something else went wrong.

        Could you use virtual data to create a simple sample pbix file and expected output.

         

        Best Regards,
        Lin