In the last two blog posts, we talked about the basics in Power BI: Data Schemas and Relationships. But as soon as you delve a little deeper into Power BI, you come across DAX.
DAX stands for Data Analysis Expressions and is the Formula language of Power BI (as well as other Microsoft tools).
DAX formulas can get quite complicated, but with the help of LLMs like ChatGPT they are much easier to master - and I would like to show you this today using a simple example.
Let's first look at a common scenario when using Autodesk Data Exchange. The Autodesk Viewer supports federated views - i.e. the aggregation of multiple data exchanges in the 3D view. For simple scenarios, the Data Exchanges have a „Federated View“ column that can be used to link several Data Exchanges together. To do this, simply drag the individual fields onto each other in the model view (step 1 below). As soon as this relationship is active, the corresponding *.viewer columns can be added in the viewer visual (step 2 below):
However, this is a simple relationship and quickly reaches its limits, as it does not enable more complex data evaluations - such as the evaluation of both data exchanges in other visuals. For example, let's say we want to evaluate the Revit categories from both exchanges - this is not easily possible with the current data model. Of course, we can create separate evaluations, and both will also be interactively connected to the viewer - provided the viewer has been created as in the screenshot above:
To be able to perform evaluations across both tables, i.e. to create a visual that lists categories from both models, we need to merge them. It is not possible to have more than one active connection between different tables - and there are several approaches on how to proceed in this case. I have had very good experiences with working out the solution together with ChatGPT and evaluating different options. Of course, it is also possible to use other LLMs - but in my experience, ChatGPT currently offers the best explanations and makes the fewest mistakes when creating DAX formulas.
So if you are unsure how something works in Power BI - just briefly explain to ChatGPT how your data is structured and what you want to achieve, you will be surprised by the result.
So if you're unsure how something works in Power BI, just briefly explain to ChatGPT how your data is structured and what you want to achieve - you'll be surprised by the result.
After a few conversations, I used the following prompt to generate the DAX code for this case:
You are a Power BI and DAX expert. Create a new table called Combined that combines data from the tables 'HVAC' and 'STR'. The new table should: - Include a column called RevitCategory based on [Revit Category Type Id] - Include a column called FederatedViewerMapping based on [Federated Viewer Mapping] - Include a column called Discipline to identify the source and use the original name of the table as value for each element - Only include rows where RevitCategory is not empty
And here is the result of this prompt:
Combined =
FILTER (
UNION (
SELECTCOLUMNS (
HVAC,
"RevitCategory", HVAC[Revit Category Type Id],
"FederatedViewerMapping", HVAC[Federated Viewer Mapping],
"Discipline", "HVAC"
),
SELECTCOLUMNS (
STR,
"RevitCategory", STR[Revit Category Type Id],
"FederatedViewerMapping", STR[Federated Viewer Mapping],
"Discipline", "STR"
)
),
NOT ( ISBLANK ( [RevitCategory] ) )
)
Now you just have to create a new table, enter the code and finally connect the Data Exchanges to this new table (they should no longer be directly connected to each other):
You can then use this table to fill other visuals:
All other required values can of course be copied using the same principle - this procedure is much more efficient and performs better than copying all available data, as the DAX code can be expanded at any time.
If you're keen to find out more, we'll be holding a webinar in a few days where we'll be taking a closer look at how we can integrate more data and create 4D simulations directly in Power BI. Don't miss it! 🙂