3 TABLE - Many to one and one to many relationship and match row by row from another table

[MrSujay]

There are 3 tables Score, Class, and point.
please check the data and relationship in the below screenshots.
I am trying this on huge data.

Note:   1. I can't change the relationship among tables.

            2. I can't create another table using the merge query.

            3. I have to add measure/column into table point which refers value from table Score.

Please let me know how I can achieve this using the DAX query.
I cant use the Merge query I think if I add measure into the table Points table visual will show data.
OR suppose if I add an index column in the score and point table then how i can join it in the DAX query?

In expected output, score is sorted in asc order with partition by Class.
Also, I got the below error while showing it on the table visual.

[v-jayw-msft · Accepted solution]

Hi @MrSujay ,

Please refer this formula:

m_score =
CALCULATE (
    SUM ( score[score] ),
    FILTER ( ALLSELECTED ( score ), score[index] = SELECTEDVALUE ( point[index] ) )
)

Best Regards,

Jay

[amitchandak]

@MrSujay , You can not have un-summarized columns from both Many size tables.

data has to group by common view bys. So add some index inside class to get that

a  new column in table

Rank= Rankx(filter(Table, [Class] = earlier([class]) , [point],,asc,dense)

Rank= Rankx(filter(Table, [Class] = earlier([class]) , [score],,asc,dense)

Now have a common table like

Rank = generateseries(1,10,1)

Join with both tables on rank and use this also as a common column in visual

[MrSujay]

Not working with rank . but i have added one index column in both point and score table then how to join it ?