Calculate measure in source or in DAX Query

[thedatahiker]

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. 

[v-yalanwu-msft]

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.

[thedatahiker]

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]

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]

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 🙂