How do I manage relationships of multiple tables in a Dataset created from an Excel.xlsm on OneDrive

[Anonymous]

Context :

I have an Excel.xlsm with tables on a OneDrive business. So I created a Dataset from this Excel via Power BI Service. Thanks to this I have created a report based on this Dataset.

Problem:

How can I manage relationships between the different tables that make up my Dataset ?

Example :

I have an "Events" table and an "Action" table. Each row of "Events" refers to several rows of "Action". They are linked by "Event_Number" fields. How do I link the "Event_Number" fields of each table ? In order to create common filters and tables ?

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

If you connect to dataset in the desktop, it will be live connection. So you couldn't manage relationships directly.

You could connect to the EXCEL file directly, or you could make some measures using SELECTEDVALUE() to create a slicer.

Measure = 
IF(SELECTEDVALUE('Table'[Column1])=SELECTEDVALUE('Another Table'[Column1]),1,0)

Then show the data whose measure is 1.

[MartynRamsden]

Hi @Anonymous 

Have a read of this article, hopefully it'll provide some answers: docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships 

Best regards,

Martyn

[Anonymous]

Thank you so much for the help.

However, it was not really helpful. I do know how to manage the relationships between the tables in classic cases. But this time the option is not available. It is probably due to the data type I used (a Power BI Dataset from an Excel.xlsm on OneDrive).