We have an SSAS tabular model as data source and currently using DirectQuery to build reports and dashboards in PowerBI. I was wondering if there are ways to filter data using DAX queries for adhoc analysis to import into PowerBI desktop if there are million + rows. It would be great to see if anyone has experience of achieving this.
When using DirectQuery you are unfortunately restricted in many ways in term of what you can do directly inside of Power BI desktop. When using this, I believe the idea from MS is that you do most of the work at the SSAS layer.
With DirectQuery you can’t do much in terms of query editing, can’t touch or see the model, and very restricted on the DAX formula you can write.
It is possible to use sql to complete the filtering and bypass SSAS, so you can use the full feature set in Power BI. This has been how I have done it a few times, and also seen it successfully done elsewhere.
On the data source screen in Power BI desktop, there is this option on SSAS database to use MDX or DAX.
Is this the place where you can write the queries to filer out data?
If yes, do you know how to filter data for a month or two from the data model itself using DAX or MDX?
Certainly please give this a go and let me know what happens.
I personally haven’t used this, I think it’s new functionality.
With the DAX queries though you must write and use table functions like FILTER, SUMMARIZE etc.
Let me know how it goes