Forum Discussion
Post ProdigyAggregate a column to join two columns (two tables)
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] ) ) )
Community ChampionMain 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] )
)
)
Post ProdigyStachu 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.