Forum Discussion
Many to Many relationship
You can use TREATAS or CROSSFITER to do the calculation, but avoid using many-to-many relationships.
In this example:
You can use the following to calculate the weight by channel (the common field in both tables is only "Item"
Using TREATAS
Sum weight by channel = CALCULATE ( [Sum Weight], TREATAS ( VALUES ( 'Sales Table'[Item] ), 'Weight Table'[Item] ) )Or using CROSSFILTER
SUM of Weight (CROSSFILTER) = CALCULATE ( [Sum Weight], CROSSFILTER ( 'Sales Table'[Item], 'Dim Item'[Item], BOTH ) )To get:
The choice really depends on the performance of each measure in your model (in my example they perform pretty much the same.
I've attached the sample PBIX file
Paul, this is what I was thinking, however, the users won't select/slice the data by Series ID. Infact the only Slicers available to the users would be [Category] and [Market Basket]. So then with a bridge table how would I get the weights? I need to calculate the average weighted indexvalue by years for each market basket
Can you show a depiction of the visual you need?
It would also be very helpful if you provided sample data or PBIX file to work with