cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JustinDoh1
Post Patron
Post Patron

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.

JustinDoh1_0-1625868006357.png

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

 

JustinDoh1_0-1625871475260.png

 

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

JustinDoh1_1-1625868440536.png

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.

JustinDoh1_0-1625873218193.png

OR I treid this way..

JustinDoh1_0-1625871011624.png

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!

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

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


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Stachu
Community Champion
Community Champion

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


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

@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
Community Champion
Community Champion

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.



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

@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.



Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.