In this article I will first give a short introduction of what Tabular Editor is and why I think that it is a very important tool in Power BI. After that I will go through five use cases where Tabular editor will help you achieve things quicker and more effective than if you were only using Power BI.
(You can also find this story at my web page eliasnordlinder.com, where I also post many other articles about Business Intelligence)
What is Tabular Editor?
Tabular Editor was created by the Danish Principal Architect and Microsoft Data MVP Daniel Otykier, working for Kapacity. He has worked with the MS BI stack for 12+ years and also being a C#/.Net developer for 15+ years.
Originally (2016) replacement for Visual Studio to edit display folders
In this video Daniel explains that the development of Tabular Editor began in 2016 while working with a client in SQL Server Analysis Server. There was a really big model, + 1000 Measures, and Daniel did not want to work with every measure one by one, as would be necessary on Visual Studio.
In Tabular Editor he could drag and drop measures between display folders instead. After this the Tabular editor have evolved a lot during the last four years to where it is today.
Offline editor, built on the JSON Tabular Object Metadata, UI in C#
It is built around the JSON Metadata and is therefore only available from 2016 SQL Server Analysis Server, as Microsoft then changed from XMLA to JSON. It is based on the programming language C# and more specifically the WinForms Application.
Originally designed for SSAS -> Now available in Power BI Desktop
It was originally designed for SSAS. But as Power BI Desktop runs on SSAS under the hood, it works perfect for Power BI as well. With the July updated of Power BI with External tools, it is now possible to fully integrate Tabular Editor and Power BI.
Not well suited for people unfamiliar with Tabular Modelling
- There is no diagram view in Tabular Editor, hard to understand completely if new to relationship view (Use Relationship view in Power BI instead)
- No DAX IntelliSense -> Good to know some DAX before
- Good to know difference between Calculated Columns and Measures.
Learn Power BI desktop first, learn the tool, basic DAX, basic modelling, and switch to Tabular Editor when it becomes more complex and bigger and you are really frustrated on how long time everything takes. — Daniel
So what is the perks of using Tabular Editor?
I will go through five perks in total, as mentioned below:
- Quick and friendly User Interface
- Calculation Groups
- Best Practices Analyzer
- Create Custom KPI
1. Quick and friendly User Interface
One of the biggest perks with using Tabular Editor is that it is designed with a much friendlier user interface, when working with the Tabular model. It is even possible to undo changes.
One can drag and drop measures easy to display folders, which is much more intuitive than in the Power BI Desktop. One can also copy and paste measures much easier as well, one can even copy the measure itself and create a copy of it.
Besides this, the DAX formatter is also integrated into Tabular Editor. If you just press the DAX formatter button it will automatically format the expression according to the rules on the DAX formatter web page.
One can also navigate to see the underlying expressions that one measure is built on, which is really helpful in complex trees. For example Sales Amount YTD might be based on Sales Amount which is based on Net Price and Total Quantity. One can then navigate from Sales Amount YTD -> Sales Amount -> Net Price/Total Quantity.
Overview of errors of all measures at the same time. The code written is also error handled by the server side when it is saved, as it is connected to a server. One can easy see when something is wrong and what that need to be changed.
Everything is written offline, therefore only synchronized to the server when one save everything. A big perk is that one therefore can do a lot of changes/create measures etc. with very high speed, and then just wait a little time when one synchronize it to the server.
2. Calculation Groups
One great feature of Tabular Editor is to create Calculation Groups. Calculation Groups can create calculations on top of existing DAX measures. One commonly used version of Calculation Groups is in time intelligence calculations.
Let’s say that you have four measures:
- Sales Amount, Total Cost, Margin and Sales Quantity.
You want to make four time-intelligence functions for each measure
- YTD Sales/Cost/Margin/Quantity
- MTD Sales/Cost/Margin/Quantity
- YOY Sales/Cost/Margin/Quantity
- PY Sales/Cost/Margin/Quantity
You must then write as much of 16 different measures in total, and this is just a small examples with original measures and four time intelligence functions. What if you had 10 measures and 10 time intelligence measures, it would then be 100 measures then you had to write.
Would it not be easier to only write the time intelligence functions and then use them dynamically for every measure that you want to use? This is exactly what calculation groups are doing.
Calculations groups is only possible to with Tabular Editor if one wants to have them in Power BI. I will not go into too much more detail, as Marco Russo have detailed it very good here. But you are creating a special table, which include one column, and then several items which are your dynamic measures/calculations.
One can then for example use this table as a slicer when having a table, one can even use a slicer.
Another good use of Tabular editor is by scripting, automate boring and time costly tasks. For example if you want to implement time intelligence measures for a lot of measures, you can do it with a script. The scripting is based on C# and more specific LINQ, but one can find a lot of script snippets at Daniels GitHub Page.
One can think of scripting similar to Macros in Excel, and just as in Excel one can reuse the same scripts for different models. Therefore, if you have one script that you really like, this can help out a lot in the long term, especially if you like coding. Below are some example of helpful scripts:
a) Create Sum measures and hiding original measure at the same time
The first script that would be interesting to do is a summation script, see below:
The script written above is such as, let us say that there is one column Sales and one column Cost. The script will take the column, make a measure and add it in the table (Therefore c.Table.AddMeasure).
The Measure will look as it will first be given a new name, Sum of Sales Amount and Sum of Cost in this case, then run the normal DAX SUM expression on it and put in a Display Folder.
Finally, there is some formatting that will take place, a description that is suited will be added and also the original Column will be hidden.
The script is executed by selecting the columns that should be converted on the left in Tabular Editor and then pressing the green arrow to execute the script.
This can also be saved as Custom Action to be used later.
b) Time Intelligence Script
The second script that could be interesting to do is a Time intelligence script, see below:
One can either make the scripts for all Time Intelligence functions and then save these as custom actions, as below. One can also have all time intelligence measures in one script and then invoke it on all measures the same time, and saved as custom action.
It will later be invokable from the tree on the left when right clicking on any measure.
There are many other scripts that can be used/created as well, such as for documentation, perspectives, ETL, and other things that should be automated.
4. Best Practices Analyzer
The Best Practices Analyzer helps you to follow best practices in Power BI modelling. It scans the model from the TOM and then, based on C# specified custom rules, decides if there are anything that doesn’t follow these rules, as above.
On the Tabular editor github page one can get the raw JSON file with rules. This can be inserted as an URL-link when clicking Tools and Best Practice Analyzer.
5. Create Custom KPI
Another neat feature you can do in Tabular Editor is to create your custom KPI, which can then be used in Tables. This is explained in a video by Alberto Ferrari. The table can look as above which I made this way.
It can be seen both if the value is above/below a specific target, and how the trend have developed over time.
KPI is an built-in function in Tabular Editor, which can be invoked by pressing right click and then Create new KPI. To see more in detail how to make the KPI expression the video linked above is very good. One have to first make a target measure, then specifiy the status and trend with DAX code.
The specific shapes for the KPI can be highly customized which is very nice.
In this blog post I have gone through what the Tabular Editor is, how it works and which very helpful features is inbuilt in the editor. Tabular Editor can help immense when doing time consuming, repetitive things in Power BI Desktop.
The interface is very quick and easy to understand which makes it easy to work with. It is also easy to include DAX Formatter which will format the code nice.
Calculation groups helps making same time intelligence features for several measures.
Scripting also helps with doing multiple time intelligence functions at the same time. But it can also help with creating summarize measure, or many other repetitive tasks much quicker.
Best Practice Analyzer helps the DAX code and modelling follow best practices.
Finally, there are other features such as KPI measures that can also be made in Tabular Editor.
Best Practice Analyzer 1:
Best Practice Analyzer 2:
Calculation Groups: https://www.sqlbi.com/calculation-groups/
Create Custom KPI: https://www.youtube.com/watch?v=xxh60Sx83zw
Des Moins Usergroup: https://www.youtube.com/watch?v=fCTmGMCE-jk
Tabular Scripts: https://github.com/otykier/TabularEditor/wiki/Useful-script-snippets
Youtube Tutorial by Daniel:
- Intro: https://www.youtube.com/watch?v=c-jZMzsvKnM
- Scripts: https://www.youtube.com/watch?v=EHs5r3XCkO8
- Best Practice Analyzer: https://www.youtube.com/watch?v=5WnN0NG2nBk
- DevOps: https://www.youtube.com/watch?v=fzZgXe3MjhI