Data display in table when 1:M relation between tables

[Anonymous]

Hi,

I have 2 tables with 1:M relation between them.  table with phased out material  is connected to more than 1 phasing in material as replacement to old material. 

example pased out material can be replaced with more than 1 material (BA14800G)

Pahsed out	Phased in
WCA31823	WC3455
BA14800G	BA1A000L
BA14800G	CA14A000L
BA14100G	PA14C0001

In Power BI want to display as 

but power bi repeating rows as below: 

Please suggest if there is a way.   Jooin on Phased out colum as available in boht tables. 

Regards,

Manveer N

[luzwales]

Not use table. should use Matrix table.

[Anonymous]

HI , Thank you for reply but if you could see the 2nd table screenshot attaced where we have multiple phase in materials against pahse out, so if we display in same table values repeated when using matrix  for phaseout material, phase in is fine. and that is misleading. 

regards,

Manveer N

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Unfortunately, the current version cannot realize this idea. Please submit your idea.

Ideas (powerbi.com) 

This is a workaround. If there are multiple rows for each category in your first table, you can try to create the following calculated columns.

Last date = CALCULATE( MAX('Table'[date]), ALLEXCEPT('Table', 'Table'[Phased out material]))

Inventory_Column = CALCULATE( SUM('Table'[Inventory]), ALLEXCEPT('Table','Table'[Phased out material]) )

Schedule_Column = CALCULATE( SUM('Table'[Schedule]), ALLEXCEPT('Table','Table'[Phased out material]) )

order_Column = CALCULATE( SUM('Table'[order]), ALLEXCEPT('Table','Table'[Phased out material]) )

ATP_Column = CALCULATE( SUM('Table'[ATP]), ALLEXCEPT('Table','Table'[Phased out material]) )

Then add the fields in the first table and the Phasing in material field in the second table to the rows of the matrix, like this:

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-kkf-msft]

Hi @Anonymous ,

Maybe you can try the matrix visual, and close stepped layout.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.