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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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.


@ 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!:
The Definitive Guide to Power Query (M)

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.


@ 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!:
The Definitive Guide to Power Query (M)

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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