Unable to Manage Relationships

[amrtly]

In Power BI service, I imported a dataset from Sharepoint and created a report from that dataset. The question is, how can I manage the relationship between each table? Unlike in Desktop, I am unable to find "Data" and "Model" menu in Power BI for web.

[amrtly]

thanks for the reply.

I've tried to do the same thing in Desktop version but still unable to manage the relationships. The "Manage Relationships" is in disabled state. There's also no "Data" Menu (refer to image attached). Do you know a way so I can manage the relationships? It happens when I choose live connection Power BI service as datasource

[Burningsuit]

Hi @amrtly 

In Power BI Desktop, you need to connect to the original data to make relationships between tables. You can't do it by connecting to an existing Power BI dataset.

In a new Power BI desktop instance go to  "Get Data" and select the data you want. You can search on "Share" for your Sharepoint connection and see you have options to connect to Sharepoint Folders or Lists.

See Create a report on a SharePoint List in Power BI Desktop - Power BI | Microsoft Docs

and  Power Query SharePoint folder connector | Microsoft Docs

Once you've pulled in the relevant tables from Sharepoint you can make relationships between the tables to work on the data.

See: Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs

Hope this helps

Stuart

[amrtly]

thanks, but my data is in excel stored in sharepoint. I want my data could be easily updated.

[Burningsuit]

Hi @amrtly 

If the data is in Excel stored in Sharepoint you can connect to it by using the Web connector in Power BI.

Open Power BI Desktop , Click on Get Data , Select Web option , then enter the URL of the Excel file stored in your SharePoint library.

The Excel file Sharepoint URL will be something like "http://tenant.sharepoint.com/sites/sitename/library/folder/excefile.xlsx"

(You'll need to enterr the tenant name, sitename and any library and folder names etc.)

Make sure you use an Organizational Account option to authenticate. Other authentication mechanisms won't work while doing this.  Sign in with your office 365 ID and password.

Once signed in, click on Connect and you will see tabs/sheets/tables from your Excel file. Select the tables you need and click on load.

Hope this helps

Stuart

[amrtly]

yeah, but the problem is i cannot manage relationships between each table

[Burningsuit]

If you connect to Excel files in this way, they will be brought in to Power BI desktop as tables into the Datamodel. If you have several tables or sheets in the Excel file, you will need to bring each table or sheet in as a table in Power BI. You will need to connect to web and bring in each individual table or sheet.

Once you have brought each table in to the Datamodel in Power BI you can then create the relationships between each table, by dragging between the related fields.

Hope this helps

Stuart

[StefanoGrimaldi]

you cannot manage relantonship on live connection, 

if its a live connection to power bi dataset you can create a composite model to add others tables and between those and the dataset tables you can manage relantionship:

https://powerbi.microsoft.com/es-es/blog/directquery-for-power-bi-datasets-and-azure-analysis-servic... 

if this helped give some kudos, if it helped or solved the problem please mark as solution for others to find it

[StefanoGrimaldi]

hey, sadly no,

you do not have that functionality in Power BI Service, only in the Desktop.  you could submit a idea to add this feature but isnt avaible via power BI service right now

if this helped give some kudos, if it helped or solved the problem please mark as solution for others to find it

[StefanoGrimaldi]

keep in mind you can do this using XMLA endpoint in a premiun workspace but that have some implications for the model that you need to consider beefore setting the XMLA endpoint:

https://www.youtube.com/watch?v=fHDDzEqtJcc&t=1317s 

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-connect-tools