Forum Discussion

Osky's avatar
Osky
Frequent Visitor
3 years ago
Solved

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:   ...
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Thanks for the PBIX, it really helps. 

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

    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