General Best Practice for Many to Many relationship?

jzhao — Wed, 01 Nov 2023 00:46:06 GMT

Originally I have a data with many to many relationship that look like the picture below:

I am told this is a bad practice, so I created a look up table that look like this.

But the problem with look up table is that custtomer nnumber in table1 does not filter up to the lookup table and then filter down to table2.

So I decide to enable by directional filter that look like this.

But bi-directional filter is also a bad design, can anyone tell me how you would design your datamodel if you want to subtract cost from sales for the same customer number.

Re: General Best Practice for Many to Many relationship?

FreemanZ — Wed, 01 Nov 2023 01:24:15 GMT

i have two ideas in mind:

1) you bring the cost column to table1, by merging table1 with table2 in Power Query. Then you have everything in one table - table1.

2) you write plot the visuals with customer number of the lookup table and a measure like:

Profit = SUM(table1[Sales])-SUM(table2[Cost])

topic Re: General Best Practice for Many to Many relationship? in DAX Commands and Tips

General Best Practice for Many to Many relationship?

Re: General Best Practice for Many to Many relationship?