Leverage external data, without duplication.

We have encountered many workflows that utilize some kind of toolchain where data coming from external datasources would be duplicated into the building information model. Tools exist that are able to synchronize data between spreadsheets and models. However, we do see various flaws in such workflows. While working in the spreadsheet it becomes very unclear which object’s data you are actually looking at because of the missing visual representation of the building element. Also, while working in the modeling application it remains unclear if all data is the “latest” and, most modelling applications simply lack a good way of visualizing data. With RTBI we try to solve this issue by leveraging the strong capabilities of Microsoft® Power BI®. In a few clicks, Power BI is able to link with many different datasources such as different spreadsheet formats, data files such as .json, .xml, … different types of databases, webservices, and so many more. In a few clicks more you are able to link these datasources by defining relationships and benefiting of the insights these linked datasources can provide.

In one of our previous tutorials you learned the basics of RTBI by bringing data of rooms in an Autodesk® Revit® model into Power BI in real-time. You also learned how to visualize that data and how to highlight related Revit elements directly from Power BI.

In this tutorial we will go into the details of linking in external data and using that to filter and colorize rooms in a Revit model.

1 – The external spreadsheet

For this tutorial we created an external spreadsheet document containing some requirements (minimal and maximal area) for each room type in the Revit model. We will bring that data into Power BI by using a standard Microsoft® Excel® connector. Once the connection has been made we also define a relationship between the Room Type column in the requirements table and the Name column in the rooms table.

2 – OK or not

With the requirements linked to each room by its type it is time to define a custom calculated column to indicate if the requirements are fullfilled or not. Our custom column will have a value of “true” if the room meets the requirement or “false” if not.

The formula for this calculated column is a regular check to see if a value from the current row lies between the values of 2 related columns in another table. Power BI recognizes the relationship of those values based on the relationship we defined in the previous step.

3 – Adapting the dashboard

Now we have a new column added to the rooms table specifying if the room fullfils the defined requirement or not, we can use that column to create new visualizations in our dashboard or to change existing ones. In our demo dashboard we will use that column as a legend for the existing bar chart, so that we can give a meaningful red or green color to each bar. As always, we can select parts in each visual (for example all non fullfilling rooms of the Instruction type) and highlight them in Revit by pressing the select button in the custom RTBI visual. (see the previous tutorial for more information on how to setup that visual)

4 – Colorize Revit elements from Power BI

An additional feature of RTBI is that it also allows users to colorize Revit elements based on any data field that is available in Power BI. The RTBI visual comes with a second property, named “Color By”, in which any data field can be dragged. Just like other Power BI visuals, this will add a “Data Colors” dropdown in the formatting tab, allowing users to specify the exact color of each unique value. After setting up the desired field and colors, pressing the colorize button in the RTBI visual will instruct Revit to colorize all elements visible in the active Revit view and falling within the current query in Power BI with the specified colors.