If you’ve been trying hard to fix the RELATED function in your Power Pivot / Power BI report, let me help you. In this post I’ll share with you some of the top reasons why the RELATED function errs and how to fix it
By the way if you din’t know, RELATED is the VLOOKUP of Power BI
#Reason 1 – The Relationship is established with a wrong Field (Column)
This is the most common mistake of all. I call it the “dragging mistake”.
- Make sure the 2 columns participating in the relationship are correct.
- Check if you’ve, by mistake linked the wrong column.
The thing to note is that Power BI will let you create the relationship till the time there are no duplicates in the Lookup Table Column, it’s does not check for the sanctity of relationship.
- You can delete the existing relationship and create a new one.
- Or double click on the relationship line and modify it!
#Reason 2 – Related Column values may not be matching
If the relationship is correct between the 2 columns then most likely the values in the fact table (column) may not match exactly with the values in the lookup table (column)
This is the primary function of bad data being imported into the data model. Common errors could include trailing or leading spaces in the unique key
- One of the options is to clean the data at the source level or
- Make some transformations at the query level (in Power Query)
Once the column values match, the RELATED function should work just fine!
#Reason 3 – RELATED does not work with Many to Many Relationships
The related function does not work with Many to Many relationships. So a Column / Measure created using RELATED function would return an error
Solution – You can either
- Work with one to many relationship
- Use Power Query to join the two tables
- Or try to use the LOOKUPVALUE function to retrieve the lookup value
Drop me a comment if your errors in the RELATED function still persist, I’ll help you out 🙂
More Power BI / DAX Tips
- How to do a COUNTIF in Power BI
- How to correctly use IF in DAX
- Using Icons for KPIs in Power BI
- How to copy and paste data from Power BI
- Relationships in Power BI