Aggregate a column to join two columns (two tables)

[JustinDoh1]

I have been struggling with this DAX code for last couple of hours, and I decided to ask for help.

I am sharing my Pbix file here in Google drive.

What I am tyring to get is, or expected output should be:

For CA:

IndexRating should be 2 and Col3 should be 126 in (June 2021).

The logic is, if there is a common value (this case: 2 for IndexRating=MainRating) & in same month in two tables, it get the IndexPoint (126 for June 2021).

For CA:

For May, the expected outcome should be 126.

For April, the expected outcome should be 125.33.

I have been trying to aggregate in this area, but I have no luck yet.

OR I treid this way..

I am curious whether I need to link these two tables on both columns (ProcessingDate & Rating (IndexRating & MainRating)), but I think it should be done in DAX to make it work..

Thanks for help!

[Stachu · Accepted solution]

Main and Index tables have many:many relationship, so you have to force filter context of one to the other, you can do that with TREATAS:

IR =
SWITCH (
    MAX ( Main[MainRating] ),
    1, "*",
    CALCULATE (
        MAX ( Index[IndexPoint] ),
        TREATAS ( VALUES ( Main[MainRating] ), 'Index'[IndexRating] )
    )
)

[JustinDoh1]

@Stachu Thank you so much for your help! This is first time hearing about "Treatas". I have a question as I am trying to understand "Treatas". Is it possible to create physical relationship in this case (M-M)? I was thinking 'bridge table' would be a solution, but I am not understanding the concept clearly.

[Stachu]

There is an excellent article regarding TREATAS:
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

As for the physical relationship the key has to be a single column, so in this case it would be combination of date & rating. That could be 1:many (with the data sample you given even 1:1) with Index filtering Main.

[JustinDoh1]

@Stachu Thank you so much for sharing your knowlege again. I attempted by creating relationship between two tables with a combined column, but it messed up previous calculations, so decided to just stick with virtual relationship for now. It also worked for this dataset as it is small.