Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
msommerf
Helper III
Helper III

Custom Function to lookup Date from another table with filter

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.
1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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"})

082405.jpg

 

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

View solution in original post

6 REPLIES 6
v-jingzhang
Community Support
Community Support

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"})

082405.jpg

 

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

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.

Glad it helps! You are welcome! 😊

lbendlin
Super User
Super User

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.

 

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 : 

 

msommerf_0-1629446148923.png

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

 

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors