{"id":101567,"date":"2024-02-28T17:00:40","date_gmt":"2024-02-28T17:00:40","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=101567"},"modified":"2024-02-24T20:26:18","modified_gmt":"2024-02-24T20:26:18","slug":"model-optimization-available-in-mdx-property","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/model-optimization-available-in-mdx-property\/","title":{"rendered":"Model Optimization: Available In MDX Property"},"content":{"rendered":"

Power BI Semantic Models have a property called Available In MDX<\/strong>. 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.<\/p>\n

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.<\/p>\n

Important:<\/strong> 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.<\/p>\n

What is the Available in MDX Property<\/strong><\/h2>\n

The internal analysis services, core of Power BI, manages the semantic model.<\/p>\n

Power BI communicates with this service using DAX<\/strong>, so it’s not affected by this property (mostly<\/em>). Excel, on the other hand, communicates using MDX<\/strong>.<\/p>\n

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.<\/p>\n

\"A<\/p>\n

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<\/em><\/strong> is enabled.<\/p>\n

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

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?<\/p>\n

What Tools you will need<\/strong><\/h2>\n

We can check how this will affect our model using DAX Studio.<\/strong><\/p>\n

We can change the property value using Tabular Editor<\/strong><\/p>\n

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

\"A<\/p>\n

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.<\/p>\n

What’s the Impact<\/strong><\/p>\n

Using DAX Studio, you can check the size of your model. After opening DAX Studio, you click on Advanced<\/em><\/strong> menu -> View Metrics<\/em><\/strong> button.<\/p>\n

\"A<\/p>\n

The metrics show information about each table and field in the model, like the image below:<\/p>\n

\"A<\/p>\n

On our example, let’s use Tabular Editor to disable the Available in MDX property on the following tables and fields:<\/p>\n

Table City:<\/p>\n