creating measure which compares values from two different tables with the same key

[roxieli123]

Hi all, 

I am currently building a dashboard which compares two datasources for Security Ratings. 

Let's say the two tables are named 'Source1' and 'Source2', both has [Cusip](which is the unique id for securities), and [RatingValue]

I want to see whether 'table1'[RatingValue] = 'table2'[Ratingvalue] for every Table1[Cusip] = Table2[Cusip], return false if they dont match, true if they match. My idea is to use dax function Related() but I havent figured out how to. 

Could anyone help with this? Greatly appreciated. 

[Nathaniel_C]

Hi @roxieli123 ,

So created two tables with your columns. However since I could not tell from your post whether all Cusip in one table are in the other, I made some different. I linked the tables in PQ as 1:1. Both tables are on the dashboard so you can see my data. The measure is at the top of the screen and below. You could break apart the IF so that you could have a third response of not in the table. Right now if there is not a match for whatever reason it is a False.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Source2 has the same rating value as Source1 = CALCULATE(IF(Max(Source2[Cusip]) = max(Source1[Cusip]) && Max(Source2[RatingValue]) = MAX(Source1[RatingValue]), "True", "False"))

[Nathaniel_C]

@roxieli123 ,

Thinking about this more, you can drop the CALCULATE()

IF(Max(Source2[Cusip]) = max(Source1[Cusip]) && Max(Source2[RatingValue]) = MAX(Source1[RatingValue]), "True", "False")

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[roxieli123]

Hi Nathaniel,

Thank you for your response! Your solution works when I only have Cusip, Source1[BondRatingCode], Source2[BondRatingCode] and the measure Compare in one table. (I have filters to show only one Cusip so it's clear to see what is happening) 

However, when I add another column [BondRating], which should be corresponding to [BondRatingCode], such that each [BondRating] correspond to a [BondRatingCode],  seen as screen shot below. 

The result table in theory shouldn't change besides having one more column [BondRating], however, after I added [bondRating], it seems like the table populated each Cusip with all possibilities of [BondRating], which makes the result incorrect,  seen in the screenshot below. Do you know how this can be resolved?  

the relationship between the two table is one-to-one on [Cusip], both direction.