Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Anonymous
Not applicable

Converting Dax from Import to Direct Query

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?

 

Column = CALCULATE(DISTINCTCOUNT(vwReportProposalOptionList[Category ID]),
FILTER(vwReportProposalOptionList,vwReportProposalOptionList[ProposalID]=EARLIER(vwReportProposalOptionList[ProposalID])),
FILTER(vwReportProposalOptionList,vwReportProposalOptionList[Option Type]="SP"))
 
Category IDProposalIDOption Type
A123SP
A123SP
A123SP
B123Standard
C123SP

 

Or if anyone has any tricks that could possibly speed up the data import I am all ears!

 

Thanks for your help!

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
v-xicai
Community Support
Community Support

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.

Greg_Deckler
Super User
Super User

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.