DAX causing system resource issues

[msommerf]

Good afternoon,

I have the following DAX formula which I have been using to find the date a vehicle was last inspected:

Last Inspection = calculate(max(bi_vw_InspectionCompleted[Inspection Completed]),filter(bi_vw_InspectionCompleted,bi_vw_InspectionCompleted[Inspection Completed]<bi_vw_wr_AssetIncidentReport[Date_Occured]),filter(bi_vw_AssetInventory,bi_vw_AssetInventory[AssetID]=bi_vw_wr_AssetIncidentReport[AssetID]))

My problem is that whilst this works with a small subset of my data, I run into memory resouce issues when more data is loaded and the report is published to the Service. This type of calculation is used alot.

I have been told that if I create a Custom Function, this will be more efficient on resources.

I have no experience with M Query.

Can anyone advise the best route to take?

Can anyone advise if there is a good book on this subject?

[v-shex-msft]

Hi @msommerf,

AFAIK, current power query functions are also not suitable for your requirement. They are good at data shaping/clean and transform data structure instead of calculation through whole table records with particular filters.

BTW, what type of data source are you worked on? If you are using SQL server or other data source that supported advanced queries, you can try to use query to add some additional fields to reduce the looping calculations.
Regards,

Xiaoxin Sheng

[danielwelch · Accepted solution]

Hi Msommerf,

While I dont have any advice with regards to M.  You could try the following table based caluclation.  Please note that this assumes that the fields available are within the same table.  If not It may be worth bringing the fields from other tables into this one through their shared ID relationship:

Calculate(Max([InspectionCompleted]),Filter(All([INSPECTIONCOMPLETEDTABLE]),

ASSETID = Earlier(ASSETID) &&
InspectionCompleted<Date_Occured ))

The calculation may run more efficiently if its does not have to look to other tables.