Better Performance with TREATAS

[aef]

Hi,

I am working on a report that used a managed model, which I am unable to edit. I have a business requirement to report on something that has no direct relationship on the model and I've been informed by the model owner that this cannot be changed.

In order to achieve this, I have created a number of measures using TREATAS, but the performance across the report is suffering. I understand that by the nature of using TREATAS, the performance will be slower, but I wondered if there are any tips to improve the below to make this run faster. For info, this is currently taking 15-20 seconds to run.

Store_Measure =
CALCULATE(
DISTINCTCOUNT(FactStore[StoreID]),
NOT (FactStore[StoreClosureReason] IN {"xx", "yy"},
TREATAS(
VALUES(ProxyCountry[CityID]), FactStore[CityID])
)

The requirement is to report by Country, but the country is not available on FactStore. 

Country Name is available on DimCountry and this is linked to ProxyCountry the model as below:

DimCountryTable ProxyCountryTable
CountryID 1:* CountryID

ProxyCountry has a CityID column, which I have used to filter FactStore using TREATAS.

[Anonymous · Accepted solution]

Hi @aef ,

Based on your problems, here are my answers.

Using TREATAS introduces additional filtering, which may impact performance. To mitigate this, consider reducing the base level in the model. Please create a relationship directly between DimCountry and FactStore.

Instead of repeating complex expressions, use variables to store intermediate results. This improves readability and performance. For example, before applying TREATAS, you can create a variable to hold the filtered CityID from ProxyCountry.

If the TREATAS logic remains constant across multiple metrics, consider creating a calculated column to precalculate the necessary values. Then, use this column in the metric values.

You can also consider the DirectQuery mode, which avoids loading all the data into memory, which can significantly improve the performance of large datasets.

Finally, you can also read this document for the further study: Propagating filters using TREATAS in DAX - SQLBI

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.