Using DAX in the Advanced Options area of Power BI Data import

[Otto_Luvpuppy]

Hi,

Does anyone know where I can find some documentation, or examples, of DAX queries being used in the Advanced Options area of Power BI data import?

I have an Azure Analysis Services data set that I want to pre-filter before I import it. The reason I'm not using Live Connection is because I want to try to reduce the data set and Live gives me the whole thing whether I'm using it or not, so I'm trying to retrieve the relevant tables only...I can do this but the data is still too large to import....hence the pre-filter.

Any ideas?

Thanks

[Otto_Luvpuppy]

Thanks for the response on this one. Yes, I kind of knew that wasn't the way to go but I'm actually trying to get around another problem by pulling the data into a dataflow as I want to restrict what is coming down the pipe. As you say the 'cube is ginormous' and my Measures are taking an age.......I'm probably barking up the wrong tree but the dataflow was worth a try I thought.
Thanks

[lbendlin]

Use Performance Analyzer and DAX Studio to improve measure performance. Use fewer visuals to improve the UX.

[Otto_Luvpuppy]

Yep...done that already. I have 80+ Measures and I'm only loading subsections of those into simple tables at the moment. Can still take 10mins+ to load. I think I need to move them to calculated columns so they are only refreshed once/twice a day. Still can't work out why they are taking so long though...not complext measures at all really.

Thanks

[lbendlin]

That's not really an option in an Analysis Services source situation. In DAX Studio look at the query plans and identify the spots where large loops are happening. Then rewrite your measures to avoid these large loops.

[Otto_Luvpuppy]

😁If only I were that proficient with Dax and Dax Studio...I'm getting there but at the moment not nearly good enough. You may as well ask me what blue smells like 😂

Thanks for your interest though...much appreciated.

[lbendlin]

DAX is used to query data for visualization. It is not an ETL/ELT language.  For that you use Power Query.

You don't really import data from an Azure Analysis Services connection. Well , you do, but only the data that is required to render the visuals on the first page the user sees.  So that rendering performance depends on the number of visuals on your page, the complexity of the measures involved in pulling the render data, and the overall performance of your data source.  If you are nimble with the measures and frugal with the visuals then you can connect to ginormous cubes with little to no delay.