calculate column in many to many relationship

[nikos_derv]

Hello!

I am trying to make a simulation tool that calculates points for money, using a conversion rate selected by user, and i am really struggling...

The point is to let user select (using a slicer) the conversion money-to-points per game, and then calculate in a column how many points are equal to their activity. I tried using a parameter but it is essensial to the rest of the exercise to use a column (not a metric) for this, so, i couldn't solve this with a parameter. If this could work, i am more than happy to hear any solution tho! 

To solve many-to-many relationship problem, i created a new table with distinct values, so, let's say my model looks like this:

The idea is to use field 'money_to_points_conversion'[Conversion_Rate] in a slicer, so user can select the desired rate and see the corresponding graphs.

I tried to insert a new column in table Activity_Per_Game:
selected_rate = calculate(max(conversion_rate)) but it always returns the max value regardless of the slicer value.

I am feeling that i am doing some fundamental mistakes here, but i really can't figure it out! Could you please help me with this?

Many thanks in advance!

[dulan_kavinda]

Hi,

This is jut to undestand about the report that you are making.

In your report you have one slicer and it is showing the conversion rate. once we select a rate the report will show all the games that available under the selected conversion rate, activity days and money played of each game. am I correct or correct me if Im wrong.

[nikos_derv]

Hi @dulan_kavinda thanks for your comment! Yes you are correct, with one minor change, there gonna be as many slicers as the number of games (it's actually just 2), so, user will select conversion rate for every game. 

[dulan_kavinda]

Did you try to connect the Game ID from the Money to Points table to the Activity table without using a separate Game ID table?

[nikos_derv]

That would make a many-to-many relationship. Money-to-points Table has multiple rows, the possible conversion rate from which the user will choose.