Comparing Values from 2 Different Tables

[aashton]

Hello,

I have an Applications table, and a Contracts table joined on Candidate ID (many to many).  The Applications table has a Source column, which I then created a new  column Source Calculated to clean this up a little.  The Contracts table also has a Source column, which I've grouped into a new field called Source Category Calculated.  I need to find where the Application Source Calculated does not equal the Contracts Source Category Calculated for the same Candidate ID.  I've tried different variations of RELATED, but keep getting the error, doesn't have a relationship to any table.... 

Contract Source Related = IF(RELATED('Contracts Signed'[Source Category Calculated])='Application Raw Data'[Source Calculated], 1, 0)

[Anonymous · Accepted solution]

Hi @aashton 

Related() function return single value from another table, it is better to use the function between the tables with relationship one-to-many, and use the related() function to the tables with table(many), you can refer to the following link.

Power BI RELATED | How to Use RELATED Function? (Examples) (wallstreetmojo.com)

The relationship between your tables is many to many, it contain to many values, so the related() fucniton cannot renturn the related single value, you need to simply your table relationship, you can creat a bridge table between two tables, you can refer to the following link.

Joining Many to Many with a Bridge in Power BI | Seer Interactive

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.