In today's blog post we will keep going forward with the "not lazy, just efficient" trend and talk about how we can make our life easier while working with shared semantic models. One major limitation of live connections to semantic models is that DAX definitions are no longer visible. I do not know about you, but I am a fan of keeping too many Power BI files open at the same time...
What if we could import the DAX definition as part of the measure description (which will then be visible live connected), but without sacrificing any kind of existing descriptions (we did talk about documentation lately) and support as well, future DAX definition updates.
For this we will use C# combined with the scripting capabilities of Tabular Editor 2. This is also a great moment to thank Bernat for reviewing my C# code, as I am not by any means a C# expert.
I did mention the shared semantic models concept, previously known as shared datasets, but what is it all about? Shared semantic models promote consistency across reports, ensuring unified definitions and calculations, therefore eliminating data discrepancies. Plus, they save the developer's time by reducing the need to recreate or maintain multiple versions of the same data model, making updates and scaling easier.
In short, we create a data model with the measures included, which is then being leveraged by multiple Power BI reports. You can read more about this in an older but amazing article.
This will be the magic code we will be working with:
1// Initialize a counter for updated measures
2int updatedMeasuresCount = 0;
3
4
5//Optional step of formatting all measures
6Model.AllMeasures.FormatDax(shortFormat: true);
7
8
9foreach (Measure m in Model.AllMeasures)
10
11{
12 // Define the special character and the separator
13 string separator = "\n***\n";
14
15 // Check if the current description already contains the separator
16 if (m.Description != null && m.Description.Contains(separator))
17 {
18 // Split the description into static description and old expression
19 var parts = m.Description.Split(new string[] { separator }, System.StringSplitOptions.None);
20 // This step will store the current text description in order to preserve it
21 string staticDescription = parts[0];
22
23 // Append the new expression to the static description
24 m.Description = staticDescription + separator + m.Expression;
25 }
26 else
27 {
28 // If there's no separator, it means there's no old expression to remove
29 m.Description = (m.Description ?? "") + separator + m.Expression; }
30
31 // Increment the counter as the measure description was updated
32 updatedMeasuresCount++;
33 }
34
35 // Inform that formatting is complete and how many measures were updated
36 Info("Formatting is complete." + "\n" + updatedMeasuresCount + " measures were updated.");
NOTE
If you see any older script that uses a structure like the one below, you should try to avoid it and move towards batch processing of DAX Formatting in order to avoid having the requests throttled. This article explains it in more detail. What not to do:
foreach(var m in Model.AllMeasures)
{
// DON'T DO THIS
m.Expression = FormatDax(m.Expression);
}We are going to focus on Tabular Editor 2 which is a free open-source version that you can get from this link. You can install it either through the msi installer (admin access will be required), or by getting the portable version. You can read more about Tabular Editor scripting here.
The code output is structured like this:
The delimiter is used to allow running the script after any update, preserving the actual measure description along with the DAX definition.
Let's start by opening Tabular Editor 2 by going to the External tools pane (if you have installed TE2 on your machine) or by opening up the portable version (in which case you should set up the connection manually).
After that we will navigate to the C# Script tab1 and paste the code2 and afterwards click the Run3 icon.
When it finishes you will get a pop-up message confirming the completion then you need to save the changes to the model (Ctrl + S).
You can also save this script for future use, by clicking on the + Icon button.
Afterwards you will be able to use the script again by opening the Samples dropdown.
Now let's look at an example of how we will see this in both Power BI Desktop, and also when live connecting to the model.
The beauty of using the delimiter is that we can run the script anytime, instantly reformatting the DAX definition in seconds.
Incorporating DAX definitions directly into measure descriptions is a small change that can make a big difference in how you manage Power BI reports. With the help of C# and Tabular Editor 2, maintaining these descriptions becomes effortless, and you save countless hours by eliminating the need to juggle multiple files or windows.
This solution ensures that your reports remain well-documented, up-to-date, and efficient for all users. Try it out and watch your Power BI workflow become smoother and smarter!