Custom Function to lookup Date from another table with filter

[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. 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.

[v-jingzhang · Accepted solution]

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.

[msommerf]

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.

[v-jingzhang]

Glad it helps! You are welcome! 😊

[lbendlin]

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.

[msommerf]

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.

[lbendlin]

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.