Many to Many relationship

[Anonymous]

Dear Community Members,

My fact table is Indices table and have joined the Market Basket table using many to many relationship . I am trying to achieve average weighted indexvalue by years. Market Basket is a portfolio name which has multiple indices. The Indices table has the Index Value which is the avg. monthly price for all the indices in the Market Basket table . [% of Cost Element] are weights of the index in the portfolio

Would really appreciate any ideas.  

Market Basket Table

Model View

[PaulDBrown]

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.....

[Anonymous]

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 

[PaulDBrown · Accepted solution]

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

[PaulDBrown]

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

[Thejeswar]

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?