I've got quite a small and simple model but when I mix fields from table A and table B in a (matrix table), as soon as I add a field from table B, there is duplicated rows with every possibility of unique value of the field from table B. I think it's because of the granularity of both tables which are different (table A = product | table B = distributor (which provided several products from table A)
Without fields from B (OK)
With fields from table B (NOK)
Could you please help to fix it? You would be my savior as I often meet this issue, and as the only solution to merge B into A to avoid it (without really understanding the issue, and by, therefore, increasing data size by doing so)
Indeed, they have only one assigned, which is one of the distributor from table B. Any idea about that?
I think that's quite close to this thread (but not sure how to solve it) : https://community.powerbi.com/t5/Desktop/Duplicate-rows-when-bringing-data-from-multiple-tables/m-p/...
Please see the examples below. This (below) is the correct behavior and I have the same relationship setup as you do. What you are seeing really seems like a relationship that is not working. Make sure that in Power Query you Trim and Clean the key columns in both tables.
I actually noticed that I was getting this result when I added a ranking (measure) based on a column from the fact table which is quite simple
Therefore I found this thread https://stackoverflow.com/questions/57801524/rankx-function-gives-duplicates-across-all-values-it-is... which is actually very similar.
And by modifying my ranking in such a way there are no longer duplicated rows
Well the HASONEVALUE is just a check of whether one or multiple items are in current context.
What I see as an issue is actually using the CALCULATE in the ranking expression because CALCULATE changes context. And since you're then just using a measure, you should be good without using CALCULATE and thus changing context.
Unfortunately I tried to remove the CALCULATE in the ranking measure but the duplicated rows still appeared when I added the field from table B 😞
Were you able to reproduce this behaviour with your test report?
Thanks again for your time 🙂
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.