- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Duplicated rows in matrix table probably because of relationship and granularity
Hello,
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)
Model
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)
Thanks !
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It may indicate that the relationship is not working properly.
In table A - does each item have only 1 distributor assigned?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If someone could provide with some further explanation I would be really grateful.
Thanks in advance 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @JirkaZ
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 🙂
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
07-11-2024 08:35 AM | |||
07-05-2024 07:38 AM | |||
02-27-2024 04:29 AM | |||
10-29-2023 11:28 AM | |||
10-10-2023 04:56 AM |
User | Count |
---|---|
103 | |
75 | |
46 | |
39 | |
32 |
User | Count |
---|---|
163 | |
90 | |
66 | |
46 | |
43 |