Hi,
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.
Regards.
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.
Interesting.
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
I am actually trying to accomplish a similar task. Instead of direct query, I want to filter before importing data from analysis services into powerbi. Note, I am using import mode, and want to limit the data I am pulling in. Right now it take a ridiculous amount of time due to how much data it imports by default (i.e. 10 fiscal years, tens of millions of rows). I believe I know how to filter by fiscal year using DAX, nothing with MDX. That said, I have no experience doing it in that window.
Can someone give me a push in the right direction here?
Thanks,
Nick
Can you push the filtering of the data back to the database layer. I really think this is your best bet here.
In many previous engagements I’ve completed I have done just this.
I the far more effecient way to do it on huge datasets because these technologies have been build to manage this type of thing in a superior way to Power BI.
Power BI is better in many other ways but this is maybe one of it’s limitation when dealing with really large datasets.
Using DAX (or MDX, which is just a formula language) isn’t going to be able to really help here. More of the work in reality just need to be done at the database layer.
That’s all the advice I have at the moment.
Chrs
Sam
Sam - Thank you for the reply. Are you certain that this is not possible to do at the import stage? I want to say that I read it was possible could accomplish the pre-filtering in the highlighted box below (MDX or DAX Query section when getting data)? I could look to push this back to the database level, but I imagine would take significantly longer to make this happen as my firm is not sophisticated in this arena. Any other resources or references would be greatly appreciated.
Thanks,
Nick
Hi Nick,
Apologies for the delayed response. If you want to import using DAX or MDX for a set of days, the following worked for me.
Assuming you have a Time/Date dimension table, the following MDX can be used to filter out one month data.
SELECT NON EMPTY{
[Measures].[Sales],
[Measures].[Quantity]
} ON COLUMNS,
NON EMPTY CROSSJOIN(
{[Coutnry].[State].[State]}
,{[Time].[Date].[Date]}
)
ON ROWS
FROM (
SELECT {
[Time].[Date].&[2019-01-01T00:00:00]:[Time].[Date].&[2019-01-31T00:00:00]
} ON 0
FROM [Sales]
)
Hope this gives you a starting point.
Hello - Thank you for the reply! I am illiterate to MDX. Given the below date table, how would the code look if I wanted to filter by Fiscal year - Period? Also, would there be a way for me to incorporate an additional filter, by branch number? See below. Thanks!
I’m not sure I said that it can’t be done. I guess what I’m suggesting is just the best way to do it.
If you choose to use DAX (or MDX) to work with millions of rows in Power BI you are going to have very slow speeds, that’s just the reality.
It is much better to get some support from IT and do that changes you need to your data more or the database layer if you are working with huge datasets.
At least attempt to get the data to a more manageable size, then Power BI can do the work for you at that point.
Sam
Hi Sam - Thank you for the reply. I have learned that I don’t necessarily need to filter before querying the data, as the data issue I was experiencing was happening at the report level. I applied some filters to the queries to reduce the amount of data applied to the report, and it has helped quite a bit.
I just watched your video on query parameters, and I hope to go a step further and set up parameters to accomplish this going forward (as opposed to placing filters on columns). This brings me to a new question…
Is it possible to multi-select parameters as opposed to only a single value? Specifically, is it possible to set up a parameter that allows me to select one, two, three, etc. operating unit numbers to be applied to the report? (see below snip-it).
I’m not actually sure this is possible (I could be wrong). I’ve done some further reading on this and can’t find anyone else who has written about it.
What I would recommend though is create another dimension that allows you to group these numbers into groups that you can send through to a parameter and create the filter you need.
Sam