Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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] )
)
)
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] )
)
)
@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.
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.
@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.
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |