Microsoft Power BI lets you analyze and visualize raw data with relationships between two or more tables. However, it is frustrating when the relationship isn’t working as intended.
If you are experiencing this issue, fear not. Here, you will find the troubleshooting steps that will help you to identify and fix the Power BI relationship not working problem.
Let’s dive in.
What Does Relationship Mean in Power BI?
When you need to merge and analyze data from multiple tables within your dataset in Power BI, the relationship feature is the key that makes the connections. The relationships in Power BI are essential for making engaging and insightful reports and visualizations.
Usually, you will find three main relationship types when working in Power BI: one-to-one, one-to-many, and many-to-many.
In One-to-One (1:1) relationships, the primary and related tables can have only one matching record.
On the other hand, in One-to-Many (1:N) relationships, you can connect a primary table’s row to one or more rows with the other table.
In Many-to-Many (M:N) relationships, it is possible to interconnect any number of records in both tables. However, you will require an additional table to show the relation between the other two.
How to Troubleshoot Power BI Relationship Issues
After importing data tables and making relationships in Power BI, it can sometimes not function due to some issues.
Why are Power BI relationships between two tables not working?
It can happen for duplicate or blank values or conflicted data types. In addition, the incorrect direction of the relationship is another primary reason you can encounter the relationship malfunctioning. Incorrect data formatting or cardinality properties also lead to relationships not working.
Here are the steps to troubleshoot the Power BI relationship is not working:
1. Ensure there is a Relationship
If you see a repeating number pattern in the Power BI report view, it is how a broken relationship should show up.
To debug the issue, it is best to check whether you made a relationship between the models, especially for a complicated model like the image below.
The relationship feature will not work if you do not make a relationship between two tables in the first place. I have made this simple mistake multiple times, which is also a possible case scenario for you.
Go to the model and verify which model lacks the relationship.
If you find one, create the relationship, and the measure will work.
2. Check the Relationship Status
So, you find that there are no missing relations between the tables, but still reporting wrong reports with repeating numbers. You should verify whether the relationship is active if that’s the case.
The easiest way to spot this is to look at the relations line. You will see a dotted relation line instead of a solid one when the relationship is inactive. As the relationship is not active, it is not working.
To active the relationship status in Power BI:
- Go to the Model View.
- Click on Manage relationships.
- Select your model and click Edit.
- Tick box Make this relationship active.
- Hit OK.
Now, check the relationship is working again in Power BI. If the issue persists, keep reading.
3. Verify the Direction of Relationship
The direction of the relationship is crucial to make the relationship work. When you interconnect two tables in Power BI, it will display an arrow in which direction the relationship works.
If the direction is set to reverse direction, the relationship won’t work and you will not get the intended results in Power BI.
Generally, the relationships in Power BI flow downhill. But by mistake, if you set the flow uphill, the arrow will point to the reverse direction, and due to that the look-up table and data table relationship will be broken.
Here are the steps to verify the direction of the relationship in Power BI:
- Click on the vertical ellipsis of a table in the Model view.
- Select Manage relationships.
- Choose Edit.
- Select the correct Cross filter direction from the drop-down and hit OK.
4. Validate Data Type
Sometimes, after creating relationships, you will see that it is not working due to incorrect data type.
For example, you make a relationship between month and sales and it will display nothing for the wrong data type. Just a blank month and the whole value of the count rows.
If the Order date is set to Text data type, it won’t show anything.
To correct the data type, select the data you want to edit, expand Data Type, and select the correct one for your case.
After that, you will see that the relationships are back, and it loads all the data in the table.
5. Update Power BI
Microsoft tends to release Power BI updates to fix glitches every month. An outdated Power BI desktop app can cause your relationships feature not to work. So if you are using an old app, consider updating the app.
Follow the process below to update Power BI:
- Go to the Microsoft official site and download Power BI.
- Install the latest app.
You can also use the Microsoft Store to download the Power BI desktop app and update through it.
If the related function is not working, your relationships will also not work, so you must carefully type the function.
Final Thought
You have to use the relationship feature to interconnect two data and visualize it through Power BI. However, it will show invalid or blank data if the relationship is not working.
In this article, I showed how to restore the relationship functions in Power BI. If you have any further queries, please leave a comment.
Have a nice day!
