Forum Discussion

msommerf's avatar
msommerf
Helper III
5 years ago
Solved

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.
Need Help
Show and Tell
Tutorial Requests
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

6 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    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's avatar
      msommerf
      Helper III
      5 years ago

      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.

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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's avatar
      msommerf
      Helper III
      5 years ago

      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's avatar
        lbendlin
        Super User
        5 years ago

        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.