Model Optimization: Available In MDX Property

Power BI Semantic Models have a property called Available In MDX. Each field in the model has this property and we can only see or change its value through XMLA. For example, we can use Tabular Editor for this.

We can disable this property in the fields where it will not be needed (and in some cases, it may be many) and we will achieve some level of model optimization.

Important: The benefits are easier to notice when the model is in Import mode. All the examples here use Import mode. On Direct Query or Direct Lake, it’s more difficult to notice benefits.

What is the Available in MDX Property

The internal analysis services, core of Power BI, manages the semantic model.

Power BI communicates with this service using DAX, so it’s not affected by this property (mostly). Excel, on the other hand, communicates using MDX.

Excel allows the user to build pivot tables using the data from the semantic model. The user can choose which field will be positioned in columns, rows, or values, such as in the pivot table illustrated bellow.

A screenshot of a spreadsheet

Description automatically generated

Here is the rub: For the field to be used in columns or rows, it needs a hierarchy, and this hierarchy is only built if the property Available in MDX is enabled.

If we disable this property, the user can’t add the field in question as column or row in an Excel pivot table.

On the other hand, do you notice how many fields in your semantic model will never be added as columns or rows in an excel pivot table?

What Tools you will need

We can check how this will affect our model using DAX Studio.

We can change the property value using Tabular Editor

Power BI .Tips portal has a tool to install external tools in Power BI, it’s the easiest way to do it. Once installed, the external tools will appear in power bi menu, like the image below

A screenshot of a computer

Description automatically generated

Once you have tools like DAX Studio and Tabular Editor installed, you can open a model in Power BI and click on the tool. The tool will be connected to your model.

What’s the Impact

Using DAX Studio, you can check the size of your model. After opening DAX Studio, you click on Advanced menu -> View Metrics button.

A screenshot of a computer

Description automatically generated

The metrics show information about each table and field in the model, like the image below:

A screenshot of a computer

Description automatically generated

On our example, let’s use Tabular Editor to disable the Available in MDX property on the following tables and fields:

Table City:

  • WWICityID
  • CityKey
  • Location
  • LastRecordedPopulation

Table Fact_Sale:

  • SaleKey

Once we open the model in Tabular Editor, we will be able to navigate through the model, like the image below:

A screenshot of a computer

Description automatically generated

We select the fields which we would like to change and modify the property in the properties window.

A screenshot of a computer

Description automatically generated

After disabling the property, we can check the metrics in DAX Studio again.

A screenshot of a data analysis

Description automatically generated

PBIX File Size

Our example uses Import mode, making it easy to notice the impact on the PBIX file size. Of course, it’s not only a matter of size, but memory consumption on Power BI service and processing.

File size before the property changes:

File size after the property changes: