Duplicated rows in matrix table probably because of relationship and granularity

[aso_be]

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 !

[JirkaZ]

It may indicate that the relationship is not working properly. 
In table A - does each item have only 1 distributor assigned?

[aso_be]

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/...

[JirkaZ]

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. 

[aso_be]

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 

daily_lost_sales_total_days_ranking =

    RANKX(

        ALLSELECTED('reports product_stock_class_daily'),

        CALCULATE(

            [lost_sales_since_no_stock]

        )

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

daily_lost_sales_total_days_ranking =

 VAR ranking =

IF(

    HASONEVALUE('reports product_stock_class_daily'[nb_days_since_last_sales]),

    RANKX(

        ALLSELECTED('reports product_stock_class_daily'),

        CALCULATE(

            [lost_sales_since_no_stock]

        )

    )

)

 RETURN

 ranking

To be honest, I don't really understand why it was happening, and how it is solved with this modification.

If someone could provide with some further explanation I would be really grateful. 

Thanks in advance 🙂

[JirkaZ]

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.

[aso_be]

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 🙂