Better Performance with TREATAS

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.