Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
103 | |
100 | |
78 | |
69 | |
63 |
User | Count |
---|---|
141 | |
106 | |
101 | |
85 | |
72 |