Improving direct query performance from cube - calculated columns

[shuhn1229]

Hi all,

I am working with a SSAS cube and developing a report which will leverage direct query. One of the columns in the cube isn't quite what id like so a I created a relatively simple calculated column that gives the intended result. The problem is this absolutely kills performance, the visual (a simple histogram) takes significantly (>10 seconds) long to load, especially when slicers change. Is there anyway around this? I believe this issue is caused because of the direct query strategy, which i really can't get around without importing pieces of the cube (which seems pointless). the calulcated column is just 

IF(Table[Task] = "(blank)", 'Table'[Col1], 'Table'[Col2])

Thanks

[Anonymous]

Hi @shuhn1229 ,

Based on your description, the calculated column formula you've provided seems straightforward, yet it's having a significant impact on performance. This can indeed occur with DirectQuery because each interaction with the report prompts new queries to the data source, which can be particularly heavy when involving calculated columns.

1. Sink the calculations to the previous step. If possible, attempt to move the logic of the calculated column to the cube itself, where the calculation can be processed during the cube's processing time rather than at query time.

2. For IF optimization, I usually use <> instead of =.

IF(Table[Task] <> "(blank)",  'Table'[Col2],'Table'[Col1])

I'm guessing this may not be as effective. The probability is that the problem is in the number of iterative calculations required.

3. Use Performance Analyzer: Utilize the Performance Analyzer tool in Power BI Desktop to identify bottlenecks and see which part of your report is causing the delay.
Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Micr...

4. For further insights, I recommend reviewing the Optimize DirectQuery performance sections in the Power BI documentation.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group