Start analyzing the spaces of your BIM models with Microsoft Power BI and SeveUp app

Before starting your BIM dashboard creation with Microsoft Power BI Desktop

In this post, we will see how to prepare the BIM data of your template downloaded from SeveUp app, add visualization modules in the Microsoft Power BI Desktop dashboard to start analyzing the data of your BIM spaces. We will finish with some design elements to enhance your BIM dashboard.
The specific Microsoft Power BI Desktop tips you will learn are :

1. Create a table by reference in Power Query part
2. Filter data in a table in Power Query part
3. Merge 2 tables (left outer join) in Power Query part
4. Create relations between tables in Model part
5. Create groups of value between tables in Report part
6. Add and set basic visualization modules in Report part

Before starting, I invite you to :

• Load a BIM model on SeveUp app
• Download the prepared template from SeveUp app (blank template)

You can also read our post on the presentation of Microsoft Power BI Desktop

Prepare your data in Microsoft Power BI (Power Query part) according to the use case

First step is to open Power Query by clicking on Transform data in the toolbar and again on transform data

Create AssetsSpaces table in Microsoft Power BI (Power Query part)

First, we will create a reference from Assets table

• Right click on Assets table
• Reference

In this sequence, the basic Power Bi Desktop move that you will learn is to duplicate a table by reference inside Power Query

Second, Rename the new table Assets (2) inti AssetsSpaces

• Right click on Assets (2)
• Rename

it’s smart to rename properly the table, My advice here is to choose a name that you are, with this name, able to understand quickly what data, the column you will find in the table

Third, we will keep only ifcSpace by sorting Class column. The goal here is to isolate only Spaces that you have in your BIM model

Create QtoSpaces table in Microsoft Power BI (Power Query part)

First, we will create a reference from QTO table

• Right click on QTO table
• Reference

Second , we will rename the new table Qto (2) into QtoQpaces

• Right click on Qto (2)
• Rename

Third , we will merge Column Class from Assets table into QtoSpaces. In this step, you will see How to merge 2 tables in Power Query , here we will use left Outer join option. There are several other options to merge tables with of course different results.

• Select the column (grey color = selected) AssetId from QToSpaces
• Choose the second table as Assets
• Select the column (grey color = selected) AssetId from Assets
• and fially click on OK

• Expand the table Assets with Class column

• Keep only IfcSpace by sorting Assets.Class column

Create QtoSpacesNetFloorArea table in Microsoft Power BI (Power Query part)

First, we will create a reference from QtoSpaces table

• Right click on QtoSpaces
• Reference

Rename the new table QtoSpaces (2) by QtoSpacesNetFloorArea

• Right click on QtoSpaces (2)
• Rename

Keep only Net Floor Area by sorting Name column

Now we will merge Net Floor Area Value in the AssetsSpaces table. In AssetsSpaces, click on merge

• Select the column ( grey color = selected) AssetId from AssetsSpaces
• Choose the second table as QtoSpacesNetFloorArea
• Select the column ( grey color = selected) AssetId from Assets
• and click on OK

Expand the table QtoSpaceNetFloorArea with Value column

Now,we will merge Net Floor Area Unit in AssetsSpaces table. In AssetsSpaces , click on merge

• Select the column ( grey color = selected) AssetId from AssetsSpaces
• Choose the second table as QtoSpacesNetFloorArea
• Select the column ( grey color = selected) AssetId from Assets
• and click on OK

Expand the table QtoSpaceNetFloorArea with  Unit column

You can close Power Query and save changes. all tables are now prepared for this Use case

Wait for loading

Check and set up relation between table in Microsoft Power BI Descktop (Model part)

Open the section Model in Microsoft Power BI desktop

Click on Manage relationship

In this part, the goal is to check relations between tables, especially for the new tables just created

	Cardinality	Cross Filter direction
Assets(AssetId) ==> AssetsSpaces (AssetId)	One to one (1 :1)	Both
Assets(AssetId) ==> AssetTree (ChildAssetId)	One to many (1 :*)	Both
Assets(AssetId) ==> AssetTreeSpatial (ChildAssetId)	One to many (1 :*)	Both
Assets(AssetTypeId) ==> AssetTypes (AssetTypeId)	Many to one (* :1)	Both
Assets(AssetId) ==> Classification (AssetId)	One to one (1 :1)	Both
Assets(AssetId) ==> Materials (AssetId)	One to many (1 :*)	Both
Assets(ModelId) ==> Models (ModelId)	Many to Many (* :*)	Both
Assets(AssetId) ==> Properties (AssetId)	One to many (1 :*)	Both
Assets(AssetId) è Qto (AssetId)	One to many (1 :*)	Both
Assets(AssetId) ==> QtoSpaces (AssetId)	One to many (1 :*)	Both
Assets(AssetId) ==> QtoSpacesNetFloorArea (AssetId)	One to many (1 :*)	Both

Create the front (UX) of your dashboard in Microsoft Power BI Descktop (Report part)

Create groups of value for simplify the space category selection

In this section, we will see How creating a group of value in Power BI Descktop, the goal here is to simplify the categorization of spaces to be more clear for sorting and selection space category

• Open the assets table and create a group on LongName

• Right click on LongName
• New group

Use « CTRL » for multi selection and click on groups

Create all groups according to your need and click on OK

Add power BI Visualization

To begin, we will add pie chart. Click on pie chart

Move and Re-size as your need

Select the visual by clicking on it

Drag and drop your data

• LongName(groups)from AssetsSpaces in Legend
• QtoSpacesNetFloorArea.Value from AssetsSpaces in Value

Now, we will add Advance card visualization module . But before add it , we have to download it from the web by clicking on get more visual in Microsoft Power BI Descktop (Report part)

Click on the Advance Card Visualization to add it

Drag and drop data in the Advance card visualization

• Value from QtoSpaces in data Field
• Unit from QtoSpaces in Postfix field

Set up the advanced card Visualization

Change the dysplay units from Auto to None

Turn off Category Label and Turn on Postfix Label

Turn on Tittle

Add a Function on Tittle

About function, in that case, we will animate the title of the card by the value of the column. It will be “name”, you can also use the function on the title to animate value color.

Add the column Name from QtoSpaces

We will see in this step How to add a filter on the advance card visual, the goal will be to display only one parameter inside a card and duplicate this card by changing the filter to have another parameter display in the advance card

Open the filter section

Drag and drop Name from QtoSpaces in the filters on Visual section and select Net Floor Area

Duplicate Advance Card with different filter

Select the visual and Copy / paste

Change Filter from Net floor area to Gross perimeter

Switch value from Sum to Average for height advance card

Add a slicer for sort by level

Move and re-size it

Drag and drop IfcBuildingStorey from AssetTreeSpatial in the Field of the filter

If the table AssetTreeSpatial is not available in your pre-made template please see this blog post to fastly add it How to add manually AssetTreeSpatial table in power query

Change filter style as a dropdown , not a list

Create the front (UI) of your dashboard in Microsoft Power BI Descktop (Report part)

Add a global background-color

Insert an image

Set up the SeveUp BIM 3D viewer custom visual design

• Set up the background color
• Set up the default selection color
• Set up the Unselected opacity

Note: If you want more detail on How customized the BIM Viewer 3D SeveUp custom visual for Power BI desktop, you have a specific post on our blog for that

If you liked this article, then please subscribe to our Youtube channel for video tutorials. You can also find us on Linked-in.