create a relationship between 2 tables from an unique dataset.

[fvdyoshida]

Hi everyone,

I have no much experience on Power Bi and I am facing a hard time here, I hope you could help me on this.

I have a Power BI dataset created from an excel file hosted on a Sharepoint site. Tis dataset has 2 tables (Raw data & General info). When I use the power bi desktop to create the new report and I select this dataset created on Power Bi portal I can see both tables available, but I cannot create the relationship between them. All options are in light gray. 

when I start to populate the report, and I add fields from both tables I got an error message statng that something went wrong, and try to fix. When I keep only fields from one table, the data is showed.

Any idea on what am I missing?

[Anonymous · Accepted solution]

Hi @fvdyoshida 

I think you want to build relationship between two tables which you got from power bi dataset.

When you get data from power bi dataset, the connection mode will be live connection.

Currently we couldn't manage relationship in live connection mode.

However, you can try to use DirectQuery for Power BI datasets. 

For more details you may refer to this blog:

Using DirectQuery for Power BI datasets and Azure Analysis Services (preview)

DirectQuery for Power BI datasets and Azure Analysis Services (preview)

Click make change to this model in blue in right corner.

Data model will change to DirectQuery.

It is a preview option. So there may be some imperfections.

You can build relationships between tables in the same datasource or different datasources.

It is a preview option. So there may be some imperfections.

Just like when you build relationships between two tables from the power bi dataset, you can't delete it.

However relationships between different datasource, you can delete them after you build them.

You can submit a new idea to improve the Power BI: Idea
It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

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. 

[Anonymous]

Hi @fvdyoshida 

You need to go to the model and select "Manage Relationships" as below.

Select "New" on the popup window and select the tables and columns that are related.

[VijayP]

@fvdyoshida 

Power BI Dataset is Predefined Data Model and it will not allow to do any further modelling!

you need to access the previous Dataset in Power BI desktop and connect both the tables to setup relation ship!

you can also share what error you are getting while trying to create report from current Dataset