Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Osky
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:

 

Osky_2-1667231626191.png

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:

 

Osky_3-1667231767200.png

 

Osky_5-1667231827415.png

 

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:

Osky_14-1667234462579.png

 

Giving result to this:

 

Osky_6-1667232353950.png

 

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. 

 

Osky_13-1667233934731.png

 

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
PaulDBrown
Community Champion
Community Champion

Thanks for the PBIX, it really helps. 

Ok, here is how I would go about this. First the model:

model.jpg

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:

result.jpg

 

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
PaulDBrown
Community Champion
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?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

 

 

 

 

PaulDBrown
Community Champion
Community Champion

Thanks for the PBIX, it really helps. 

Ok, here is how I would go about this. First the model:

model.jpg

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:

result.jpg

 

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

 

Many thanks for your help.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.