Forum Discussion
Multiple relations
- Thanks so much, If it Work i ‘ll tell you
Rows: Productos[prodcode]
Columns: calendar[fecha]
Values: sum off ‘cantidad from detcargas and ‘cantidad from ops.
The relations are in the image attached
I want obtain a matrix with dates from several tables.
"detcargas" contain the quantity of product that has been load in a determinate date.
"ops" contain the quantity of product that has been produced in a determinate date.
"Product" it's the table that contain all products of a factory, and "Calendar" is a calendar :)
The relations are:
productos[prodcodigo] with ops[op_producto_codigo]
productos[prodcodigo] with detcargas[dcarproducto]
calendar[fecha] with detcargas[dcarfecha_cam]
calendar[fecha] with ops[op_fecha_planificada]
my goal it's obtain a matrix that show the evolution the stock per day, for it, i need a matrix which rows will be all products from "table products", the columns will be a date range from "calendar table" and the values will be an acumulate sum from quantityes of products produced (ops) minus quantityes of product loaded (detcarga)
thank
Okay this is how I would solve this. I would remove the inactive relationships and only relate tables in one direction.
Ex: 1 ->* and not bi directional. This will speed up the performance of your report.
Second I'd create a distinct Product Code Table (=Distinct('productos'[prodcodigo])) to relate to productos, ops, and decargas.
Use the Products field in the product code table as the rows in your matrix. That shoudl solve your circular dependancies.Then use a measure as your value to something like SUM(detcargas[dcarproducto])-SUM(ops[op_producto_codigo]).
If that dosen't work i may need your pbix to try and solve it.