Forum Discussion

JustinDoh1's avatar
JustinDoh1
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Aggregate a column to join two columns (two tables)

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!

Need Help DAX
  • Stachu's avatar
    Stachu
    5 years ago

    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] )
    )
)

4 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      JustinDoh1
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      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.