Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I was able to import data from a data base and build my formula and expressions that I needed for a report. My issue is that I am importing so much data that it is taking an exceptionly long time for it all to load and then more time for the formulas and expressions to do their work.
From my understanding Direct Query works faster with larger sets of data but it comes with limitations on what you can do.
Is there a way to convert the following expression to work in a Direct Query?
Category ID | ProposalID | Option Type |
A | 123 | SP |
A | 123 | SP |
A | 123 | SP |
B | 123 | Standard |
C | 123 | SP |
Or if anyone has any tricks that could possibly speed up the data import I am all ears!
Thanks for your help!
Solved! Go to Solution.
Well, this should work as a measure, is there a reason you absolutely need a column?
Measure = CALCULATE(DISTINCTCOUNT(vwReportProposalOptionList[Category ID]),
FILTER(vwReportProposalOptionList,vwReportProposalOptionList[ProposalID]=MAX(vwReportProposalOptionList[ProposalID])),
FILTER(vwReportProposalOptionList,vwReportProposalOptionList[Option Type]="SP"))
Put that into a visualization along with your Category ID and Proposal ID.
Your formula is kind of the trifecta of DAX DirectQuery incompatibility. None of your functions are supported in a column and one of them, EARLIER is specifically incompatible with columns and measures. Nifty. That being said, not sure how that formula was working as a column without some ALL statements in it to break it out of row context.
Hi @Anonymous ,
Greg_Deckler's suggestion is great.
In addition, if you need to improve the performance using Import mode, see Data Import Best Practices in Power BI.
For more information on optimizing data sources for DirectQuery, see DirectQuery in SQL Server 2016 Analysis Services.
You can also configure incremental refresh, Incremental refresh can significantly reduce data refresh duration, especially for large model tables. See more: Incremental refresh in Power BI Premium .
You can refer to the links to see more performance tips:
https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance,
https://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques,
https://maqsoftware.com/expertise/powerbi/power-bi-best-practices.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Well, this should work as a measure, is there a reason you absolutely need a column?
Measure = CALCULATE(DISTINCTCOUNT(vwReportProposalOptionList[Category ID]),
FILTER(vwReportProposalOptionList,vwReportProposalOptionList[ProposalID]=MAX(vwReportProposalOptionList[ProposalID])),
FILTER(vwReportProposalOptionList,vwReportProposalOptionList[Option Type]="SP"))
Put that into a visualization along with your Category ID and Proposal ID.
Your formula is kind of the trifecta of DAX DirectQuery incompatibility. None of your functions are supported in a column and one of them, EARLIER is specifically incompatible with columns and measures. Nifty. That being said, not sure how that formula was working as a column without some ALL statements in it to break it out of row context.
Greg-
Thank you for the reply. I dont understand how half of the things I make work, but it was from my initial review. I am pretty new to the Power Bi world so it is 100% possible I am doing things the hardest way imaginable. I am going to start taking some classes come the new year, hopefully it gets me straightened out.
I got pulled away and will have to try your solution a little later today or tomorrow morning. But thank you again for the reply!
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |