Date table works on Matrix until I add another table

[Anonymous]

Hello Everyone,

Below are my current relationships. The systemusers table is connected to different activity tables by the ownerid column. I created a date table that is also connected to these activities based on a createdon date column. Both systemusers and date table aren't directly connected.

Now, if I create a matrix and put the systemuser full name as rows and date table as column it works. (note BDD is actually the fullname column from systemusers)

The values you see are just the count of each activity table. I didn't like the format and decided it was best if the activities showed as rows, beneath the fullname. 

I created a new table called activity summary in which has the same columns as the systemuser table but i added a new column that provides the activities name on each user, see example below.

As seen on my first image of the relationships, I created one between this table and the systemuser table through the ownerid and made it cross filter. 

I thought it was good to go but now the matrix gives me an error as soon as I put the date table.

I don't know why this is happening. Can you please help? I hope I've given sufficient information.

[Anonymous · Accepted solution]

Hi @Anonymous ,

'Can't display the data because Power BI can't determine the relationship between two or more fields.'

You are trying to use two or more fields from tables that are not related. You need to remove the unrelated fields from the visual and then create a relationship between the tables. Once you have done this change, you can add the fields back to the visual. Refer: Create and manage relationships in Power BI Desktop 

Here is a solved post with similar error like yours and I hope it could help you solve your problem.

For reference: Error: Can't determine relationship between fields

If this reply still couldn't help you solve your problem, please share a sample file with me.

And here I have some questions:

Where is [fullname] in Rows Fields from in second screenshot, still from "Systemusers" or "ActivitiesSummary"? 

If [fullname] in Rows Fields is still from "Systemusers", will your matrix work if you remove the new relationship?

Best Regards,
Rico Zhou

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