cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Relationship Duplicates rows in a matrix

Hi everyone,

I was trying to work with some relationship managements and I find this problem.

These are the relationship tables (and in color the fields related) that I'am working in:

Being the "numero Oferta" the active one, in the second relationship.

For every "factura" (invoice) there is one albaran (packing slip), and then in every "lineas albaran" (packing slip line) there are multiple lines assigned to an integer number, i.e 1,2,3... The thing is that every albaran has multiple lines, that I want to relate to lines of the "lineas oferta" as we can see in the next image:

The point is, that from every offer there are some articles related in every line of the offer that are not desired, and they not appear in the packing slip, i.e the offer with number 666, has 7 lines, and the packing slip 55 has only the lines corresponding 1,2,3,6 and 7.

I would like to make a table showing how every invoice is related to an offer.

These are the fields involved in the table:

Giving result to this:

The next step would be to show every descpription and price but when I set the field "descripcion oferta" from the table "lineas oferta" to view a description from the product in the packing slip, it happens a multiplication. (I dotted in red what I whould expect to see), I expected that the relation should work somehow like a filter, taking away all the products offered that were not desired at the packing slip.

The point of it would be to be able to see the inverse evolution of the proccess in the table.

Hope I could find some help, maybe I am missing some detail that I can't notice.

Thank everyone.

1 ACCEPTED SOLUTION
Community Champion

Thanks for the PBIX, it really helps.

Something you need to be aware about with this model structure is that you have to be careful when using fields from the Calendario table: you can have dates which differ from oferta -> albaran -> factura. So if you need to make calculations across the fact tables where a field from the Calendario table is involved, you might need to remove the filter from the calendario table (the higher the granularity, the more likely you need to remove the filters). You will see that I have included a REMOVEFILTERS ('Calendario') in the measures for this purpose.
If you are calculating measures for a single fact table, you don't need to worry.

Having said that, here are the measures you need to fulfill the outcome of your request:

Descripción Oferta =
CALCULATE (
MAX ( 'Lineas oferta'[Descripcion oferta] ),
SUMMARIZE (
'Lineas albaran',
'Tabla Ofertas'[Numero Oferta],
'Tablas Lineas'[Linea Albaran]
), //you need this virtual table to filter the dimension tables based on the rows provided by the 'Tabla factura'
REMOVEFILTERS ( Calendario )
)
Suma costes oferta =
CALCULATE (
SUM ( 'Lineas oferta'[Coste Oferta] ),
SUMMARIZE (
'Lineas albaran',
'Tabla Ofertas'[Numero Oferta],
'Tablas Lineas'[Linea Albaran]
),
//you need this virtual table to filter the dimension tables based on the rows provided by the 'Tabla factura'
REMOVEFILTERS ( Calendario )
)

You can now build the matrix using the fields from the dimension tables and the measures to get:

Sample PBIX file attached

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

4 REPLIES 4
Community Champion

The first thing I would do is remove the many-to-many relationships and build bridge tables for the common fields.
Can you provide sample, non-confidential data or a link to a non-confidential PBIX file?

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Frequent Visitor

Of course, all is a made up example that I find it would be an interesting case of study. Here there is the link to a we transfer for the sample on pbix.

https://we.tl/t-e9yG8Ch3eu

Community Champion

Thanks for the PBIX, it really helps.

Something you need to be aware about with this model structure is that you have to be careful when using fields from the Calendario table: you can have dates which differ from oferta -> albaran -> factura. So if you need to make calculations across the fact tables where a field from the Calendario table is involved, you might need to remove the filter from the calendario table (the higher the granularity, the more likely you need to remove the filters). You will see that I have included a REMOVEFILTERS ('Calendario') in the measures for this purpose.
If you are calculating measures for a single fact table, you don't need to worry.

Having said that, here are the measures you need to fulfill the outcome of your request:

Descripción Oferta =
CALCULATE (
MAX ( 'Lineas oferta'[Descripcion oferta] ),
SUMMARIZE (
'Lineas albaran',
'Tabla Ofertas'[Numero Oferta],
'Tablas Lineas'[Linea Albaran]
), //you need this virtual table to filter the dimension tables based on the rows provided by the 'Tabla factura'
REMOVEFILTERS ( Calendario )
)
Suma costes oferta =
CALCULATE (
SUM ( 'Lineas oferta'[Coste Oferta] ),
SUMMARIZE (
'Lineas albaran',
'Tabla Ofertas'[Numero Oferta],
'Tablas Lineas'[Linea Albaran]
),
//you need this virtual table to filter the dimension tables based on the rows provided by the 'Tabla factura'
REMOVEFILTERS ( Calendario )
)

You can now build the matrix using the fields from the dimension tables and the measures to get:

Sample PBIX file attached

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Frequent Visitor

Hi Paul, I will take a deep insight to your reply, at naked eye seems to be the ideal result I was looking for. Hence, when I process everything I will mark your reply as a solution.