The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I'm trying to lookup values from Table 1 - match to similar values in Table 2 and if a match is found, get a sum of the total amount, and if no match found, return a 0 or a blank. Since we have multiple instances of the names in each column, I created a many-many relationship with the Table 1 set to filter Table 2. However, when I try to add the sum of the Amount column to the Table 1, it looks like Table 1 gets filtered too:
Table 1:
Name |
Andy |
Bob |
Joe |
Mike |
Susan |
Table 2:
Name | Amount |
Andy | 10 |
Andy | 100 |
Bob | 1000 |
Joe | 500 |
Peter | 10 |
Peter | 5 |
Sally | 50 |
Expected Result in Table 1:
Name | Amount |
Andy | 110 |
Bob | 1000 |
Joe | 500 |
Mike | 0 |
Susan | 0 |
However, what I do get for Table 1 is:
Name | Amount |
Andy | 110 |
Bob | 1000 |
Joe | 500 |
How do I do the lookup and sum without my primary table getting filtered out? I'd really appreciate any help.
Solved! Go to Solution.
Hi, @newpbiuser01
Many-to-many relationship is not recommended here.
Please modify it as follows.
Best Regards,
Community Support Team _ Eason
Hi, @newpbiuser01
Many-to-many relationship is not recommended here.
Please modify it as follows.
Best Regards,
Community Support Team _ Eason
@newpbiuser01 , Make it bi directional and try. Having a many to many it not a good idea
Create a common table and try
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
264 | |
120 | |
115 | |
83 | |
70 |