& Construction

Integrated BIM tools, including Revit, AutoCAD, and Civil 3D
& Manufacturing

Professional CAD/CAM tools built on Inventor and AutoCAD
Connect data from multiple sources to a data exchange, and filter both the connected and exchange data in Power BI dashboards.
Transcript
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.
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.