topic Re: Custom Function to lookup Date from another table with filter in Power Query

Custom Function to lookup Date from another table with filter

msommerf — Thu, 19 Aug 2021 12:23:25 GMT

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. I have little to no experience with Power (M) Query.

Is there a way in which I could create a Custom function to perform this task?

Any assistance is appreciated.

Re: Custom Function to lookup Date from another table with filter

lbendlin — Thu, 19 Aug 2021 23:08:27 GMT

Your code is DAX, not Power Query. Install DAX Studio and learn how to use it for query optimization. As a starter you can simplify your query

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

The condition

bi_vw_AssetInventory[AssetID]=bi_vw_wr_AssetIncidentReport[AssetID]

should be covered by the data model.

Re: Custom Function to lookup Date from another table with filter

msommerf — Fri, 20 Aug 2021 07:57:52 GMT

Hi, Thank you for your response,

I will look to get DAX Studio installed as suggested. Lots to learn!

My data model consists of 3 tables:

bi_vw_AssetInventory - This contains a unique list of assets.

bi_vw_InspectionCompleted - This contains a list of multiple Inspections for each asset.

bi_vw_AssetIncidentReport - This contains a list of multiple defects found for each asset.

The 3 table are connected on the AssetID field.

If I use the DAX statement you have provided, I get an error :

Whilst I realise the code I posted was DAX, I was wondering how the same result could be reached using Power Query.

Re: Custom Function to lookup Date from another table with filter

lbendlin — Fri, 20 Aug 2021 13:42:06 GMT

Your error message indicates that you use an older version of Power BI Desktop. Please update.

Power Query is notoriously bad at joining tables. Use the data model for that - that's its job.

Re: Custom Function to lookup Date from another table with filter

v-jingzhang — Tue, 24 Aug 2021 08:26:35 GMT

Hi msommerf

Since all tables have the AssetID field, you could try below code to create a column.

Last Inspection 2 = MAXX ( FILTER ( bi_vw_InspectionCompleted, bi_vw_InspectionCompleted[AssetID] = bi_vw_wr_AssetIncidentReport[AssetID] && bi_vw_InspectionCompleted[Inspection Completed] < bi_vw_wr_AssetIncidentReport[Date_Occured] ), bi_vw_InspectionCompleted[Inspection Completed] )

If you want to try Power Query, you could add below steps to bi_vw_AssetIncidentReport query. I'm not sure whether this would run into memory resource issues when large data is loaded, you may have a try. I attach the pbix for your reference.

#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"AssetID"}, bi_vw_InspectionCompleted, {"AssetID"}, "bi_vw_InspectionCompleted", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each List.Max(let occurredDate = [Date_Occured] in List.Select([bi_vw_InspectionCompleted][Inspection Completed], each _ < occurredDate))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"bi_vw_InspectionCompleted"})

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Re: Custom Function to lookup Date from another table with filter

msommerf — Wed, 25 Aug 2021 14:37:57 GMT

A sincere thank you for going to the trouble of finding a solution by mocking up tables etc.

The DAX solution shown above has solved my issue.

I can now do an incremental refresh with a years worth of data.

👍

Thank you so much.

Regards

Mark.

Re: Custom Function to lookup Date from another table with filter

v-jingzhang — Thu, 26 Aug 2021 01:33:41 GMT

Glad it helps! You are welcome! 😊