Performance difference between calculated column or measure?

[ellac]

Hi!

I have a question regarding where one should do heavier modelling tasks - in a DAX measure, as a calculated table in the semantic model (we are using AAS - so I prefer not to make any modelling in the PBI report) or even earlier in the data tables.

I want to compare two date/timestamps for when an order was ordered and when it was delivered. We have the date/timestamps in two different columns in the same table. Right now I have solved it by building several DAX measures - but this is way too heavy as the amount of data rows grows.

I'm thinking of creating a new column in the data table in the database (before the data enters the semantic model) that should contain the comparison, i.e. a column of the time difference in hours. Or is it a better option to do a calculated column in the semantic model? How will a calculated column differ from DAX measures based on performance? Will the computation load be any different from having only DAX measure? 

Let me know if you need more information. Thanks!

/Ella

[Anonymous · Accepted solution]

Hi @ellac ,

As far as I know, there are many differences between Dax calculated column and Dax measure.

1. 

When we finish the dax code of calculated column, it will takes up resources like memory. However, measure won't takes up resources when it is finished. So we could see calcualted column directly in data view and couldn't see measure .  Measure will take cpu to calculate the result when it is used in visualizations.

2. 

Measure couldn't be added into slicer or filter levels which are higher than visual level filter, but calculated column can.

3. Dax measure will caculate aggregation values but calcualted column will calcualte based on rows.

So you need to choose dax measure or dax calculated column based on your requirements.

For refernece:

Calculated Columns and Measures in DAX

Power BI Measures vs Calculated Columns

And you can refer to this offical blog to learn more details about how to optmize Power BI data model.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.