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
thedatahiker
Employee
Employee

Calculate measure in source or in DAX Query

This is a general question that I'm trying to get a clear answer to. If you could provide any documentation that exist on this topic it would be super appreciated.

 

Is it more performant to calculate a measure like Net Profit (Profit - Cost) in the source data as a column in the fact table or within a DAX Measure  = [Profit] - [Cost].

 

By the source data, I mean: the sales fact table would have three value columns; profit, cost, net profit. In this instance, the net profit calculation would be done in the upstream data source (SQL). In general, I've understood that it's typically better to use a measure than to create a calculated column but this scenario doesn't use the Power BI calculate column. 

 

In the other scenario, my fact table would have 2 value columns profit and cost. In this instance, I would create a measure in DAX that calculates Net Profit. 

 

 

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

Hi, @thedatahiker ;

My preference is SQL, DAX Measure then DAX calculated column.
1.it can provide better compression if it's a SQL column/expression over a DAX column.
2.SQL is easier to manage change than lots of DAX columns.

3.you can add as a DAX measure then it's not going store any data and calculated when needed.

4.However measure cannot be used for slicers.

 

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This is closer to the answer I'm hoping to get. You mention that putting the column in the SQL expression can provide better compression and also that by using a DAX measure the value won't be stored in any data and calculated only when needed. Since the DAX measure wouldn't require any storage wouldn't that be a more performant solution? Especially when the fact table can reach into 10s of millions of rows?

joglidden2
Post Patron
Post Patron

I personally prefer to assemble my solutions in SQL views. This is mostly because I'm more familiar and comfortable with SQL than I am with DAX. Also, PBI/DAX has "funny" behaviour and options that I'm still not quite used to. I generally see PBI/DAX to be the "quick & dirty", and SQL to be the reliable solution for the long haul, especially if you intend to re-use your views across multiple reports. It spares you from having to rewrite columns/measures every time. 0.02

TheoC
Super User
Super User

Hi @thedatahiker 

 

In my view, and many have their own, it should depend on several key factors, among others:

 

  1. How the data is structured
  2. Level of detail the data is available (e.g. transactional or summarised)
  3. Your / your client's requirement for the report (e.g. once-off or repeatability, etc)

I will always try to use measures where possible when creating financial / accounting indicators.  They are often cleaner and easier to work with as you start to piggyback from one measure to the next.  

 

Best of luck with it all!

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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