cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roxieli123 Frequent Visitor
Frequent Visitor

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

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. 

3 REPLIES 3
Nathaniel_C Established Member
Established Member

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

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

 

Ratings from two tables 2.PNG

 

 

Ratings from two tables.PNG

 

 

Nathaniel_C Established Member
Established Member

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

@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
Highlighted
roxieli123 Frequent Visitor
Frequent Visitor

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

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) 

1.png

 

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. 

3.png

 

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. 

 

2.png

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 186 members 2,209 guests
Please welcome our newest community members: