Forum Discussion
Relationship problem after appended data
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 :(
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
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??