Filtering data in power BI recieved from Revit by category & family & type

hello dears,
my ultimate goal is to manipulate speckle 3D visual view in Power BI based on slicers that rely on data such as family name, type name, category, and instant shared parameters such as “planned date” and “actual date” which all are received from the Revit model.

the 3D visual follows the category slicer but does not follow neither the type nor family slicers except for (blanks)



I dove into the power query of the received data in Power BI and I realized the structural foundation pile family received in 3 rows one of them of Speckle_type:
Objects.Other.Revit.RevitInstance
and the remaining two:
Objects.BuiltElements.Revit.RevitElementType:Objects.BuiltElements.Revit.RevitSymbolElementType

each of them has values I need to combine them, similarly as I select an instance in Revit and all information related to one element.

speckle link: Speckle
attached Power BI file.
STA-STR-Speckle.pbix (1.8 MB)

Software Versions:
Revit 2022.1
Revit connector: 2.19-RC
Power BI: Version: 2.122.746.0 64-bit (October 2023)
Power BI connector: 2.19-RC
using Speakle.xyz as stream server.

2 Likes

@gokermu looking for your assist!

1 Like

Hey @hesham.bendary01 ,

I’m looking into it now. Will get back to you once I figure out what’s going on.

Ok, i think i know what’s going on. You have lots of instances in your model and those instances don’t directly have the “category”, “type”, “parameters”, “zone” fields you were referencing too. You can learn more about what instances are in this blog.

@jonathon shared a workaround for this problem using Power BI.

We need to relate these instances back to their reference elements/types. I converted @jonathon 's suggestion into a Power BI function. Please refer to the following guide for more information on creating custom functions:

Once you create a blank query, open Advanced Editor and replace it all with the below code:

let
    CreateRevitInstanceTable = (SourceTable as table) as table =>
    let
        // Step 1: Identify RevitInstance Objects
        RevitInstanceRows = Table.SelectRows(SourceTable, each [speckle_type] = "Objects.Other.Revit.RevitInstance"),
        NonRevitInstanceRows = Table.SelectRows(SourceTable, each [speckle_type] <> "Objects.Other.Revit.RevitInstance"),

        // Step 2: Extract Reference IDs
        RevitInstanceWithReferences = Table.AddColumn(RevitInstanceRows, "ReferencedId", each Record.Field([data][definition], "referencedId")),

        // Step 3: Select only necessary columns for the join to avoid duplicates
        SourceForJoin = Table.SelectColumns(SourceTable, {"Object ID", "data"}),
        SourceForJoinRenamed = Table.RenameColumns(SourceForJoin, {{"Object ID", "ReferencedObjectId"}, {"data", "ReferencedData"}}),

        // Step 4: Join with the referenced rows
        ReferencedRows = Table.NestedJoin(RevitInstanceWithReferences, {"ReferencedId"}, SourceForJoinRenamed, {"ReferencedObjectId"}, "JoinedTable", JoinKind.LeftOuter),
        
        // Step 5: Expand the joined data
        ExpandedTable = Table.ExpandTableColumn(ReferencedRows, "JoinedTable", {"ReferencedData"}, {"ReferencedData"}),

        // Step 7: Remove temporary columns
        CleanedTable = Table.RemoveColumns(ExpandedTable, {"ReferencedId", "data"}),

        // Rename column
        RenamedColumns = Table.RenameColumns(CleanedTable, {"ReferencedData", "data"}),

        // Final Table
        InstancesFixed = Table.Combine({NonRevitInstanceRows, RenamedColumns})
    in
        InstancesFixed
in

    CreateRevitInstanceTable

Then feel free to name the function whatever you want. I named mine “CreateRevitInstanceTable ”.

image

Then you can directly call this on your raw data:

It seemed to work once we fixed the instances:

PBIDesktop_KxySgGbx9f

I hope this helps!

Here’s the Power BI file: instancesFixes_STA-STR-Speckle.pbix (1.8 MB)

4 Likes

A post was split to a new topic: Conditional Formatting in Power BI