Forum Discussion
Relationship problem after appended data
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.
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
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!
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]))*100You 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
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 :(
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
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
