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
You should create a dimension table with unique values of the Series ID field covering the range of values present in both tables. Join the Series ID field from the new dimension table to the corresponding fields in both fact tables in one-to-many relationships. You then use the field from the dimension table in your measures, visuals, slicers, filters.....
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
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
Hi Anonymous ,
Try connecting the Market Basket table to the bridge table using the slicer ID. The Relationship should be a many to One respectively and the direction should be from Market Basket to Bridge table. That way any selection made in the columns of the Market Basket table will accordingly modify the Bridge table.
This Brige table should then be connected to the Indices table with a One to Many relationship and direction should be from Bridge table to Indices table. That way the Bridge table selection would flow to Indices table
Can you check if this works?
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
