Slow performance of DAX measure for calculating age

[AndyDDC]

Hi,

I'm struggling with a DAX issue with a measure.  I'm trying to create a measure in which I can drive which calculation is done by using a value in a lookup table. The calculation is to calculate the age of a customer using their data of birth and then another date such as order date. The solution I'm working on has multiple dates that the calculation can use, hence the lookup table in which I can select which date is used by using the SWITCH statement.

I have done this but the performance is pretty bad when I try and create a table visual using values from both the customer and sales table.

I have uploaded the PBI Desktop file and source data.  The steps I have done is:

https://www.serverlesssql.com/wp-content/uploads/2022/11/age.zip

1. Load the Customer and Sales file

2. Create a static data table and populate with values to use to drive the Age measure

3. Create a measures table and add a basic order count measure

4. Create the Age measure and add the logic to do the dates calculation.

Then when creating a report, if I try and create a table with CustomerKey, OrderDate, Age...the table never renders.  I'm not sure how to get the calculation more efficient.

If anyone is able to point me to anything to try that would be appreciated.

Thanks

https://www.serverlesssql.com/wp-content/uploads/2022/11/age.zip

[v-xiaosun-msft · Accepted solution]

Hi @AndyDDC ,

It takes time when you have very large models.

I looked at your PBIX file, your formula does not need to be modified.

Please pay attention to the "Age - Order" which is calculated by 'Sales'[Order Date]. Therefore, when you select measure in the slicer, since the calculation is iterative, and your data has too much rows, it can take a long time. And after my test, it may take a little more long time when the field is loaded the first time.

You can also use DAX Studio (free tool) to catch reasons for decreased performance.

DAX Studio - SQLBI

Best Regards,
Community Support Team _ xiaosun

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

[AndyDDC]

Hi, thank you for the reply.  What I've done is push the age calculation to the SQL Source as it's not really very useful as a measure really.