Connect and filter data from multiple sources in Power BI

00:03

When using the Power BI Connector to load and analyze an Autodesk data exchange in Power BI,

00:09

you can connect to and filter data from multiple sources.

00:13

This enables you to connect a data exchange to an external data set,

00:17

such as information in an Excel spreadsheet.

00:21

In this example, a previously created Power BI dashboard displays lighting fixtures from a loaded data exchange.

00:28

Additionally, this Excel data set includes information not contained within the source Revit model,

00:35

such as the Model column in this case.

00:38

To connect this information to your dashboard, back in Power BI,

00:42

from the ribbon, Home tab, Data pane, expand Get Data and select Excel Workbook.

00:50

From the file browser, select the Excel file containing the data set,

00:55

and then click Open.

00:57

In the Navigator dialog, select the appropriate sheet from the Excel file—in this case, Lighting.

01:05

This will create an additional table within Power BI with columns for Id, Model, and Family and Type, as shown in the preview.

01:14

Click Load.

01:16

In the Data pane, you see the added Lighting table, with Family and Type, Id, and Model columns.

01:24

On the dashboard, a donut chart has been set up to display the quantity of each specific model of lighting fixture in the exchange.

01:33

To populate this visual, click and drag Model from the Data pane into the Visualizations pane, and drop it into the field under Legend.

01:42

In the visual, you see the legend added.

01:46

Then, drag either Model or ID and drop it into the Values field to count the number of specific models within the project.

01:54

The donut chart updates.

01:57

However, notice that when you click on any of the values,

02:00

it does not automatically connect to the data from the exchange.

02:05

To connect the two data sets, you must find a common value and use it to establish this relationship.

02:12

Select Model view.

02:15

This view shows each of the data tables,

02:17

including the spreadsheet data and information from two different data exchanges.

02:23

Now, you need to identify a common value, or column,

02:27

between the Lighting table and the exchange being used in this example.

02:32

In the Lighting table, you see the ID, which is the same as the Revit ID,

02:37

and which you know is also present in the data exchange.

02:41

To determine the appropriate exchange column for the connection,

02:44

switch to the Table view for a clearer look at the available information.

02:49

From the Data pane, select the Lighting table, where you see the ID column that you are trying to match.

02:56

Now, to find the corresponding ID information in the table for the data exchange,

03:01

select the table in the Data pane.

03:04

Here, you see the Revit Element ID column, and when you scroll down,

03:09

you see that this column will allow you to connect the two tables.

03:12

To establish this connection, back in the Model view,

03:16

locate the Revit Element ID in the data exchange table.

03:20

Now, from the Lighting table,

03:23

select the corresponding ID column,

03:25

then drag and drop it onto Revit Element ID.

03:29

The New relationship dialog opens,

03:31

where you can define how the data is connected.

03:34

Expand the Cardinality drop-down to select one of the following connection options:

03:39

Many-to-One, which is the most common option and the default,

03:44

means that the column in one table can have more than one instance of a value;

03:48

while the other related table, often known as the lookup table,

03:51

has only one instance of a value.

03:54

One-to-One means that the column in one table has only one instance of a particular value,

03:59

and the same is true of the related table.

04:03

Many-to-Many is used when multiple repeated values in both tables correspond to each other.

04:08

For this example, select One-to-One.

04:13

If you encounter an error, you may need to select a different option.

04:17

In this case, there are some repeating values so One-to-Many is a more suitable choice here.

04:23

Click Save.

04:27

The connection is now established.

04:30

Back in the model view, hover your cursor over the connection line to highlight the columns that are connected.

04:37

Return to the dashboard to see the effect of this connection.

04:42

Now, when you click information on the lighting chart visualization,

04:46

the corresponding data from the data exchange is highlighted and filtered.

04:50

The reverse is true as well.

04:54

When you click information in one of the data exchange visualizations,

04:57

the relevant data is also highlighted and filtered on the lighting chart.

05:02

Now you can seamlessly connect and filter data from multiple sources within Power BI.