Forum Discussion
Relationship Duplicates rows in a matrix
- 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
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
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.