Power BI Meetups 101 – Tabular Editor

I started a meetup group to practice presenting about Power BI.
On 2021 January 26th we had the first online event about Tabular Editor.

Most of you know that a Power BI data model is actually
a local instance of an Analysis Services Tabular OLAP cube. 
This kind of OLAP technology is storing data in memory
so that querying it is much faster than reading from a disk.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enhanced-dataset-metadata
In September 2020, use the enhanced dataset metadata became (GA) generally available to store metadata of a Power BI data model. 
This format is similar to what is used for tabular models, based on
the Tabular Object Model.

This way of storing data is important because it made possible for external tools to read and write the metadata. The data model is actually described in this metadata including the relationship between tables and the DAX Measures. The tools using the XMLA endpoint are also utilizing this metadata. 

One of the functionalities of XMLA endpoint – which is a Premium feature – that it lets you change a measure in a data model in the cloud without republishing it. If you have a quite large data model it saves you considerable amount of time. Even if you have Premium capacity, the default setting is Read, not Read\Write. If you want to know more about XMLA endpoints then watch this video from Ferenc Csonka:

XMLA Endpoint: The Cornerstone for Power BI as an Enterprise Solution

Here comes Tabular Editor,
an external tool.

You could start Tabular Editor and DAX Studio before their appearance on the external tools ribbon but then you had to connect to your data model, now it starts already connected to the model you started the tool from. https://tabulareditor.com/
https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-external-tools

The author/developer of this tool is Daniel Otykier,
a Principal Architech at www.kapacity.dk.

Before we take a deep dive into the functionalities of Tabular Editor, I want to tell you why I think that you can’t avoid using it.
Have you ever forgot a logic of one of your DAX measures then opened the formula editor to read it? Well, you can only do that when you are using the PBI Desktop connecting to local data sources. How can you help the report consumers understand the calculation logic? There is a property of a DAX measure called description. It’s a place to describe the measure.
Before you release a report to the consumers you should go over every measure, check the logic, the formatting, the measure folders and give each of them a description. This description is visible to the consumer when they hover the mouse over the measure on a visual. The user manual can’t replace using this property, that would be very uncomfortable to always open a document to check the logic of a calculation.
By the way, using tabular editor you could put the DAX expression into this property but I am against that way to convey the logic.

Please save your pbix files frequently even if the use of Tabular Editor is supported by Microsoft since the GA of enhanced metadata and the release of the external tools ribbon. It’s not an issue because we already had reasons to save the files frequently.

Summary of Tabular Editor Tutorial videos From Daniel Otykier
moderated by Mike Carlo from powerbi.tips

Mike Carlo is a great moderator always helping out viewers by clarifying otherwise complicated concepts.
There are a million other useful stuff on powerbi.tips so visit the site!

The first functionality Tabular Editor had that it could move measures into and between display folders more conveniently than Desktop.

  • You can select multiple objects., CTRL+F, CTRL+H shortcuts available
  • There is an undo function that is faster than the back arrow in Power BI.
  • Very useful to copy measures because that also takes a copy of the original measure’s formatting properties.
  • Built-in DAX formatter.
  • Hide, unhide columns.
  • DAX IntelliSense in only available in the paid version.
    But there is an alert icon that shows up if the measure was not successfully created on the model. Blue mark means unsaved change.
  • Can visualize dependencies between measures.*1
  • Can create translations.
  • Can edit M queries that do not change the structure
  • Can create perspectives and KPIs.

*1 – Note from Bertalan: This is a great example for getting to know these tools because I remember once creating creating a query from a Dynamic Management View to list measures and their dependencies, this was a rather slow solution. It was a don’t invent the wheel example.

Using Scripting

Scripts are written in C# but it is quite simple to understand the syntax without any programming knowledge. There are useful code snippets in the documentation (link above).
Fortunately, there is a limited IntelliSense helping us authoring these scripts.If you want to learn the basics of C# then I (Bertalan) really recommend this course on Udemy:
https://www.udemy.com/course/csharp-tutorial-for-beginners/

Use cases:

  • Auto-generate measures from columns
  • Reuse scripts

We are modifying objects in the Tabular Object Model (TOM) (link also above).
The hierarchy in which you see the tables and other objects are reflecting the TOM.
https://docs.tabulareditor.com/Useful-script-snippets.html

I think the possibility to select multiple columns to generate multiple measures with a script is only very rarely useful for me because I very rarely use simple measures with a simple SUM or COUNT or even a CALCULATE with a predicate, therefore I always have carefully write each of these. 
I have worked with reports where I had to create multiple measures using the same pattern with different predicates, filters. In these cases, I would use a calculation group.

There are for loops – written with the “foreach” syntax – that iterate over an objects child-objects and apply a set of codes. I would separate the task of hiding a column and creating measures because I often used the keys of the fact table for validation, searching for orphaned keys causing blank rows in the visuals.

Best Practice Analyzer

https://github.com/TabularEditor/BestPracticeRules/blob/master/BPARules-PowerBI.json

The feature is checking your model against a set of rules defined in this Json file. You can reference the file from Daniel’s GitHub or
download it and modify it if you want.

You can read them. If you don’t agree with one or more rules,
you can set the Analyzer to ignore those.
My favorite is “Revisit TODO expressions”. If you think that you might want to mark your DAX measure for reviewing later you can put a //TODO comment in the expression. These measures would be found by the analyzer as long as the comment is still there. The “Don’t summarize numeric columns” rule is also a timesaver, note: you can disable implicit measures on the model level. When you create your first calculation group, that would disable these implicit measures, that’s one more reason against implicit measures.

DevOps

I love Azure Devops, even though I have only started using
the Boards and repos in my private tenant to put tasks
like “read this book” on the boards
and I use repos to keep backups of my presentation notes but I don’t have work experience with it so I have to admit that I can’t accurately summarize Daniel’s video about this topic.
If you are interested, you can watch it on YouTube.
Oh, it seem like Daniel has a series about Azure Devops here:
https://tabulareditor.com/2019/02/20/DevOps1.html
I will have to create a task for myself on my Azure Board
so that I would not forget about going through it later.

Tabular Editor free and paid

In the future we will have access to the free version and
there will be a paid commercial version as well.
The paid version will have an evaluation period to try what’s like authoring DAX with the best DAX editor ever invented.

Currently the preview version of the paid version of Tabular Editor is being tested by users who got a license key for the product along with Microsoft employees and the pricing is still in the planning phase.

You can check out the paid version in this video:

Walkthrough of Useful script snippets

Let’s go over these snippets by ourselves now! By looking and understanding these you will able to write new snippets. If you have some basic C# knowledge, that’s even better.

Export object properties to a file

Copied from documentation and changed the filename:

// Construct a list of all visible columns and measures:
var objects = Model.AllMeasures.Where(m => !m.IsHidden && !m.Table.IsHidden).Cast<ITabularNamedObject>()
      .Concat(Model.AllColumns.Where(c => !c.IsHidden && !c.Table.IsHidden));
// Get their properties in TSV format (tabulator-separated):
var tsv = ExportProperties(objects,"Name,ObjectType,Parent,Description,FormatString,DataType,Expression");
// (Optional) Output to screen (can then be copy-pasted into Excel):
// tsv.Output();
// ...or save the TSV to a file:
SaveFile("C:/Python/documentation_2021-01-24_12_39.csv", tsv);

Let’s look at the output:
It’s an amazing way to automate documentation!

exported documentation

I know kung-fu or do I ?
Come on, V!
Let’s create some quickhands!
We have a data model to build!

So if I paid attention to the syntax of these advanced expressions,
can I write some myself?

As I said earlier, what I find really useful is not creating dozens of measures
by the click of a button but storing patterns in these scripts. You probably have some notes of DAX patterns that you often use or often look up patterns from the Definitive Guide of DAX book or the DAX patterns website or book. Now you can translate those DAX patterns to advanced scripts and save them in a folder.

Let’s look at a simple Divide function that I created. It might be too generous to call this a pattern, it’s so simple. Always use the DIVIDE function instead of just using the division sign (/) even if there is zero chance that the denominator is going to be zero (pun not intended).

You could say that it is absolutely unnecessary to put the denominator and the nominator in variables but in case you need to debug your measure, it becomes useful that you can replace the Result variable with a variable from above to see what it outputs. The “\n” are new line creators in C#.

var Denominator = Model.Tables["_Measures"].Measures["Cost"];
var DenominatorName =  Denominator.Name;
var DenominatorCode =  Denominator.DaxObjectName;
// Template for DIVIDE measure
foreach(var m in Selected.Measures)
{ 
  var newMeasure = m.Table.AddMeasure(
   m.Name + "/ " + DenominatorName, // Name
   // start of DAX expression 
   " \n VAR Result = "
   + " \n DIVIDE(" + m.DaxObjectName + "," + DenominatorCode + ") "
   + " \n RETURN Result " ,    
   // end of DAX expression 
   m.DisplayFolder     // Display Folder
                                    );
newMeasure.FormatString = "#,##0.00 %";
// Copy format string from original measure   
newMeasure.Description = m.Name + " divided by " + DenominatorName;        }

My second advanced expression I had written is the ShowValueForDates
https://www.daxpatterns.com/standard-time-related-calculations/

To put it shortly it is a measure that shows whether there is a value in the fact table for a given date in the Date column. Can be used to only show YTD or Moving total values when there is value for the given time period.
For example in February only show YTD for February and do not repeat the February value for the rest of the year.

// Creates a ShowValueForDates column for every currently selected column and hide the column.
foreach(var c in Selected.Columns)
{
    var newMeasure = c.Table.AddMeasure(
    "ShowValueForDates " + c.Name,                    // Name
    " \n VAR LastDateWithData = \n CALCULATE ( MAX ( " +  c.DaxObjectFullName +" ), REMOVEFILTERS () ) \n VAR FirstDateVisible = MIN ( 'Date'[Date] ) \n VAR Result = FirstDateVisible <= LastDateWithData \n RETURN Result "// DAX expression
    "TODO"                        // Display Folder
    );
        // Provide some documentation:
    newMeasure.Description = "Filtering. True when in the current context of the date dimension there is a value for the selected column. \n FirstDateVisible <= LastDateWithData ";
 }

Moving Total
Based on this measure, I built a Moving Total Annual advanced script translating the Moving Annual Total measure from DAX Patterns: https://www.daxpatterns.com/standard-time-related-calculations/

var M1 = Model.Tables["_Measures"].Measures["ShowValueForDates DateKey"];
var M1Name =  M1.Name;
var M1Code =  M1.DaxObjectName;
var Increment = -1;
var Period = "YEAR";
var DFolder = "TODO";
// Template for Moving The moving annual total
foreach(var m in Selected.Measures)
{ 
  var newMeasure = m.Table.AddMeasure(
  m.Name + " Moving total for " + m.Name + " " +  Increment + " " + Period, 
  "\n IF ( " + M1Code + ", \n CALCULATE ( " + m.DaxObjectName + ", \n DATESINPERIOD ('Date'[Date],MAX ( 'Date'[Date] )," + Increment  + "," + Period + ")) \n )" // DAX expression 
                                    );
newMeasure.DisplayFolder = DFolder;
newMeasure.FormatString = m.FormatString;
// Copy format string from original measure   
newMeasure.Description = m.Name + " Moving total for " + m.Name + " " +  Increment + " " + Period; 
}

Handling perspectives

The topic of personalized views and perspectives belong together.

If you are one of the Ancients who built Atlantis Multidimensional models
like I am, then you are already familiar with perspectives. They show you a selection of items useful for you.

Patrick’s personalised visuals with perspectives

I really liked Patrick’s video so I’ll also use a Pie Chart to demo creating a perspective.

In my model I have two fact tables, Inventory and Sales. The Channel dimension is only related to Sales so I should hide it in a perspective and call it Inventory. First I create two perspectives, Inventory and Sales.

I click on the Channel table, hide it in all perspectives, then click on Show in Perspectives – and select the Sales perspective. I also Click on the Sales Amount related measures and show them in the Sales perspective.

I have only one Inventory measure that I put in Inventory and hide in Sales.

The step to choose a perspective for a give report page is so easily forgettable, it’s on the page level properties.

The perspective only shows the columns and measures that are related to the Sales report page.

Creating calculation groups in Power BI Desktop using Tabular Editor by Marco Russo:

Read more about calculation groups here:
https://www.sqlbi.com/articles/introducing-calculation-groups/

Creating KPI In Power BI Desktop

Thanks for reading! Be excellent to each other!