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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Peavey
Helper II
Helper II

Average of colums in Direct Query

In import mode I just make a new column with the average calculation, but now in Direct Query I cannot use average when making a expression for a new column. What is the best approach to acheive this?

 

Capture.JPG

 

Thanks,

-A-

6 REPLIES 6
amitchandak
Super User
Super User

@Peavey , You have to think the same in terms of measure.

In case it is simple Avg

Avg Measure =  Average(Table[A])

If that was a calculation at the row level , try like

Avg Measure = AverageX(Table,Divide(Table[A],Table[B]))

@amitchandakclose but not quite. 

 

I have 10 rows I need to average. Tried your solution and it works on fex. 2 rows. But when trying all 10, I get the error message: Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your quesry, and try to simplify them.

 

Any ideas?

@Peavey , this a complex formula for 10 rows?

 

How many rows does the table have ?

@amitchandakthere are 10 tables with 62 rows. 

One row is one week. I need the average on each week from each table.

Tahreem24
Super User
Super User

@Peavey ,

You can't create calculated column in PBI with Direct Query mode. But you can create measure. There are some limitations please refer the below image and link:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

Capture.PNG

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Peavey ,

For more deets, please refer the below link;

https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-direc...

Capture.PNG

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.