Uses of Tabular Editor in Power BI
Introduction
The Tabular Editor is a data modelling tool for Power BI and Analysis Services Tabular. It includes many features like a premium UI, DAX Editor with IntelliSense capabilities, offline metadata analysis with syntax/semantic checking, diagram view, table preview, pivot grid view, workspace mode, etc. It is an offline editor and is built around the JSON Metadata of the data model loaded. You can connect to your .pbix files(using the Analysis Services
instance running) and Power BI Premium Datasets(XMLA Endpoint) from Tabular Editor and analyze and modify the model. The changes you make on the Tabular Editor are written to the model once you save it back to the connected database.
This tool can be mainly used to optimize the dashboard thereby reducing the size of the dashboard & improving the dashboard performance. The below optimizing features are available in Tabular Editor :
• Calculation groups — A collection of items, which are basically the same measures that you create in your report but are created in a slightly different way
• Partitioning concept can be applied on huge datasets which will improve refresh performance of a model
• IsAvailableInMDX property for each dataset allows you to stop attribute
hierarchies from being built on columns where they serve no real purpose.
This tool comes into picture when a developer start experiencing dashboard performance issues due to complex DAX queries\measures in their dashboards and due to huge dashboard sizes. There are different ways how these measures can be optimized using tabular editor which are discussed in detail under Calculation Groups section below.
Similarly a huge dataset and dashboard size is really hectic to load and refresh as far as a developer is concerned. Partitioning and “IsAvailableInMDX” properties in tabular editor helps resolve this issue to a great extent, the details of which are present in sections below.
Connecting to Power BI
a. You can connect to Tabular Editor from the External Tools ribbon on the Power BI Desktop file.
b. You may open the model also from the Tabular Editor as below:
Go to File > Open> Model from DB
On the window that opens, select the Local Instance (of the Analysis Service)
of the Power BI file that is running.
In case the model is present on a Power BI Premium workspace, you can
connect to the respective dataset of the report using the XMLA Endpoint.
Click OK to load the model.
Calculation Groups
Calculation groups are a robust modeling feature in Power BI that reduces redundant measures created by grouping standard measure expressions as calculation items. The calculation group is like a table with DAX expressions for each of the calculation items.
Creating Calculation groups:
Step 1: In Power BI, go to External Tools > Tabular Editor
Step 2: Right click the Tables folder and choose Create New > Calculation Group. You can name it to whatever you want.
Calculation group Use cases
- Reduce number of measures
Let us consider a scenario where you need time-related calculations like YTD, MTD, QTD for sales amount and profit amount measures. Here, in general, we will be creating time calculations of each measure resulting in 6 calculations.
Here we are only creating calculation on top of two measures- Profit amount and Sales amount, but if we need to do the same set of calculation for more number of measures then the number of calculated measures will also increase. To avoid this the developer can use Tabular Editor
With Tabular Editor:
To avoid the redundant calculation, we can create a calculation group for time-related calculations and use it with any base measure.
Step 1: Create a calculation group and name the New Calculation Group whatever you want (Ex: Calculation)
Step 2: Under the Calculation Group ‘Calculation’ create Calculation Items. Right-click on Calculation group and then click on New Calculation Item.
Step 3: Create individual calculation items for each time calculation like below:
Step 4: Click on the ‘Save’ icon, which will save the changes to the connected database and the Power BI Desktop. You will get a warning to manually refresh whenever you modify the calculation group, like adding or deleting a calculation item.
Step 5: In Power BI on refreshing, the calculation group will be available for usage.
You can drag and drop this calculated group (E.g. Formula) in a matrix or slicer with a base measure
We can use the same calculated group even if we need to do the same calculation on top of a different measure for another visual. The only modification that we need to make is changing the measure.
2. Switch measure
Consider a scenario where we need to change what measure is represented in a visual using a slicer. To do that first we need to create an input column for slicer and then need to create a measure for toggling the visual based on the slicer.
The main drawback of this approach are as follows.
• Need to create a new table for slicer.
• Will not be able to modify the table later.
• The measure will be more complex based on the number of rows in the created table
With Tabular Editor :
Calculation groups offer a more robust alternate approach for switching between measures being displayed. Using the custom format strings that can be defined against each calculation item, you can set a measure to display as intended.
3. Dynamic format strings
Consider a scenario were the end user need to change the “display unit” and “value decimal places of each underlying visual”. The only way to do formatting without using tabular editor is going to field formatting tab of the visual and modifying the settings manually.
This will be a time-consuming task and the user will not be able to modify this using any slicers. This can be easily accomplished using Tabular editor
With Tabular Editor:
We can make use of calculation group to format the values displayed in visuals to Thousand, Million, Billion Etc. as per the user selection.
4. Dynamic labelling
Used for highlighting a data point in a visual such as Highest, Lowest, top 10, bottom 10 by make use of format string expression in tabular editor. We can dynamically change the data labels on the visual.
DAX functions specifically for calculation groups:
1. SELECTEDMEASURE — Used by expressions for calculation items to reference the measure that is currently in context. In this example, the Sales measure.
2. SELECTEDMEASURENAME — Used by expressions for calculation items to determine the measure that is in context by name.
3. ISSELECTEDMEASURE — Used by expressions for calculation items to determine the measure that is in context is specified in a list of measures.
4. SELECTEDMEASUREFORMATSTRING — Used by expressions for calculation items to retrieve the format string of the measure that is in context.
5. Precedence
Precedence is a property defined for a calculation group. It specifies the order of evaluation when there is more than one calculation group. A higher number indicates greater precedence, meaning it will be evaluated before calculation groups with lower precedence.
6. Ordering
For each calculation item you want to order, set the Ordinal property to a positive number. Each number is sequential, for example, a calculation item with an Ordinal property of 1 will appear first, a property of 2 will appear second, and so on. Calculation items with the default -1 are not included
in the ordering but will appear before ordered items in a report.
VertiPaq Analyzer in Tabular Editor
In the analyzer metrics, you’ll see how tables and columns work, and determine which entity needs to be more optimized and improved. If you want to find out which part of the code slows down your code’s performance, use the VertiPaq Analyzer.
- View >> VertiPaq Analyzer
2. Click on Collect Stats option to View the analyzer
