Relationship problem after appended data

[limewire]

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 calulated column in my report. Before I appended my data with date from other years, this had all worked! With the relationship between the two tables being "one to many". Now that there are more years, my relationship is spoiled!  How do I fix this?

[vanessafvg]

@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]

@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

[vanessafvg]

@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]

@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

[limewire]

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]

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]

@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]

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

[limewire]

@v-lili6-msft I don't think it's possible to share this data and trying to recreate this mess is an especially daunting task for someone as inept as myself but let me try to explain it once more! I appreciate your patience. 

From my orignial query, I birthed 2 other queries, CCEOI & Net Views. I have filtered both of these queries. Before I added more data, these two queries had a many-to-one relationship using the "Program/Network" column. I was able to have a calucated column between the two queries using other columns in the queries. The forumla for the first column looked like All_Total Viewer Values = DIVIDE (CCEOI [All], RELATED ('Net Views' [All])*100). I was able to use this formula for my other columns and it worked perfectly. 

Now that I have added more data, the relationship between CCEOI & Net Views had to be changed to many-to-many when using the "Program/Network" column. Using the formula you suggested earlier, All_Total Viewer Values = DIVIDE (CCEOI [All], CALCULATE(MAX('Net Views'[All')))*100  the values are incorrect, seeing to be 0.00 or 0.01. These strange values match the output of your other suggestion All_Total Viewer Values = DIVIDE(CCEOI [All], CALCULATE(MAX('Net Views'[All]),FILTER(RELATEDTABLE('Net Views'),'Net Views' [Year]=MAX('Net Views'[Year])))*100 . 

When I match CCEOI & Net Views on a column other than "Program/Network", I can get a one-to-many relationship yet still am not able to reproduce the calculated column I had before.  I have also created another query which is just "Program/Network" in an attempt to connect the queries CCEOI & Net Views that way but no success thus far. 

@parry2k So very sorry to bother you but perhaps you can help as well or have some ideas?? 

[parry2k]

@limewire Just emailed you to connect when you have time to take a look.

[parry2k · Accepted solution]

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