Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Inefficient Code

(0) ShareShare
ReportReport
Posted on by

Hi, 

 

I have the following tables: REPORT_DETAIL, OBJECT_DETAIL, REPORT_OBJECT_LINK, OBJECT_LEVEL_LINEAGE, EDW_TO_ROOT_OBJECT_MAP. 

 

The model is: model.PNG

 

I have a gallery to display records in OBJECT_DETAIL and I am filtering this gallery based on a dropdown of EDW_Project_Names. When an EDW_Project_Name is selected from the dropdown and the value is NOT "TBD", I want object records to display if:

1) they are directly linked to a Report record in REPORT_DETAIL for which the EDW_Project is the EDW_Project_Name

 

2) they appear anywhere in an OBJECT_LEVEL_LINEAGE record for which the Master_Object_Id of the lineage is associated to an object that is directly tied to a Report record for which the EDW_Project is the EDW_Project_Name

 

3) It is an EDW_Object_Id associated mapped to a Root_Source_Object_Id where the Root_Source_Object_Id is the Root_Object_Id for a record in OBJECT_LEVEL_LINEAGE for which the Master_Object_Id of the lineage is associated to an object that is directly tied to a Report record for which the EDW_Project is the EDW_Project_Name. 

 

Additionally, I have a selection of radio buttons that, depending on which is selected, the data source changes (ie OBJECT_DETAIL vs OBJECT_DETAIL_ROOTS). 

 

I know that I can create a SQL Server stored proc and pass in the parameter of Edw_Project_Name, but this would involve Power Automate and be a Premium feature. I am trying to avoid using that feature. 

 

I have written the below code and it works, but is EXTREMELY slow and I'm sure very inefficient, as there are a lot of repetitive chunks. I am looking for suggestions on how to optimize this code.

 

If the value is "TBD", then I display all Object records from OBJECT_DETAIL that are NOT tied to any Reports or Objects through any of the above relations. This logic is achieved with a SQL view since it is a static query and does not require any parameters or stored proc flows.

 

I would also like to have alternate row coloring, which is why everything is wrapped in the ForAll with the RowNo patch. 

  • Verified answer
    Re: Inefficient Code

    I ended up just using SQL Server stored procs and Power Apps Flows to achieve what I was trying to do. Way more efficient and run times reduced from ~11,000 ms to ~700 ms. 

  • Re: Inefficient Code

    Hi @Anonymous. Thanks for looking at this. I've saved the code as a .docx and attached it. 

    This is hosted on SQL Server. It is a Premium Power App but I am trying to avoid using Power Automate and stored proc flows if at all possible.

    I am able to crate additional views if required. 

    Thanks.

  • Re: Inefficient Code

    Hi @RandyHayes. Thanks for giving this your consideration. 

     

    OBJECT_DETAILS_ROOTS is a view of the OBJECT_DETAIL table that only selects records where the Object_Ids appear at the Root level of an Object Lineage in the OBJECT_LEVEL_LINEAGE table. It's essentially:

    SELECT *
    FROM OBJECT_DETAIL d WITH(NOLOCK)
    WHERE d.Object_Id IN (
    SELECT OBJECT_Id
    FROM OBJECT_DETAIL d WITH(NOLOCK)
    INTERSECT
    SELECT l.Root_Source_Object_Id
    FROM OBJECT_LEVEL_LINEAGE l WITH(NOLOCK)
    )

     

    Roots, RootsNoEDW, and NoLineages are all booleans triggered true/false depending on the radio button selection by the user. Similar to how OBJECT_DETAIL_ROOTS was created, OBJECT_DETAIL_ROOTS_MISSING_EDW_EQUIVALENTS becomes the data source if RootsNoEDW is true to show Object records from OBJECT_DETAIL_ROOTS where there are no Root_Source_Object_Id matches in EDW_TO_ROOT_OBJECT_MAP. This view query is:

    SELECT *
    FROM OBJECT_DETAIL WITH(NOLOCK)
    -- GET ROOT OBJECTS ONLY
    WHERE OBJECT_ID IN (
    SELECT OBJECT_ID
    FROM OBJECT_DETAIL WITH(NOLOCK)
    INTERSECT
    SELECT Root_Source_Object_Id
    FROM OBJECT_LEVEL_LINEAGE WITH(NOLOCK))
    AND
    -- GET OBJECTS WITHOUT EDW EQUIVALENTS
    OBJECT_ID IN (
    SELECT OBJECT_ID
    FROM OBJECT_DETAIL WITH(NOLOCK)
    EXCEPT
    SELECT Root_Source_Object_Id
    FROM EDW_TO_ROOT_OBJECT_MAP WITH(NOLOCK)
    )

     

    NoLineages is similar to the above but selects a subset from OBJECT_DETAIL where there are no related records in OBJECT_LEVEL_LINEAGE. the view query is:

    select *
    from OBJECT_DETAIL with(nolock)
    where object_id in (
    select Object_Id
    from object_detail with(nolock)
    except
    select Master_Object_Id
    from OBJECT_LEVEL_LINEAGE with(nolock)
    )

     

    The Items property of ProjectPicker_1 is EDW_PROJECT.EDW_Project_Name.

  • RandyHayes Profile Picture
    RandyHayes 76,287 on at
    Re: Inefficient Code

    @Anonymous 

    Can you provide more details on the following:

    - OBJECT_DETAILS_ROOTS (Is this the Object_Details table?  Not seeing it in your model)

    - Roots

    - RootsNoEDW

    - NoLIneages

    - The Items property of ProjectPicker_1

     

  • RandyHayes Profile Picture
    RandyHayes 76,287 on at
    Re: Inefficient Code

    @Anonymous 

    Just wanted to let you know that I am reviewing your formula.  It is *extremely* overcomplicated and can be reduced to just a few lines.  But, I need to review it thoroughly to grasp what you are doing. 

    You are repeating so many functions over and over on the same data - this is not only a nightmare to maintain, but will be highly inefficient. 

    Give me some "digesting" time on it. 

  • Re: Inefficient Code

    Oh boy, I will do my best here, could you please share the same in a file formatted please?

     

    Some additional questions, 

    --where is this hosted? looks like SQL server, if so the power app would be considered Premium

    --if hosted in SQL DB, would you be able to create additional views if required? (not that I'm considering this, but I need to know) 

  • Re: Inefficient Code

    This is the current code I am using:


    With(
    {
    Objects: If(
    // EDW Project is TBD
    ProjectPicker_1.Selected.Result = "TBD",
    ForAll(
    Sort(
    Search(
    OBJECT_DETAIL_UNATTACHED,
    box_Source_Object_Search.Text,
    "Object_Combined_Name"
    ),
    Object_Combined_Name
    ),
    {
    Object_Id: Object_Id,
    Object_Name: Object_Name,
    Object_Type: Object_Type,
    Server: Server,
    Database: Database,
    Schema: Schema,
    Object_Combined_Name: Object_Combined_Name,
    Obsolete_Code: Obsolete_Code,
    Reported_Code: Reported_Code,
    Notes: Notes,
    Created_Person_Id: Created_Person_Id,
    Created_Ts: Created_Ts,
    Last_Updated_Person_Id: Last_Updated_Person_Id,
    Last_Updated_Ts: Last_Updated_Ts
    }
    ),
    // else EDW Project is NOT TBD
    If(
    Roots,
    ForAll(
    Sort(
    Search(
    /*-------------------- begin code block to filter objects by edw project -----------------*/
    Filter(
    OBJECT_DETAIL_ROOTS,
    Object_Id in
    // directly linked to a report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id Or// in any part of an object lineage for an object that is directly linked to a report in EDW project
    Object_Id in// Level 1
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // make sure Master Object directly linked to report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level1_Object_Id Or Object_Id in// Level 2
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level2_Object_Id Or Object_Id in// Level 3
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level3_Object_Id Or Object_Id in// Level 4
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level4_Object_Id Or Object_Id in// Level 5
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level5_Object_Id Or Object_Id in// Level 6
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level6_Object_Id Or Object_Id in// Root
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id Or//Object is EDW object mapped to a root object in a lineage for an object directly linked to report in EDW project
    Object_Id in// EDW Mapping
    Filter(
    EDW_TO_ROOT_OBJECT_MAP,
    Root_Source_Object_Id in
    // lineage where master object directly linked to report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id
    ).EDW_Object_Id
    )
    /*-------------------- end code block to filter objects by edw project -----------------*/,
    box_Source_Object_Search.Text,
    "Object_Combined_Name"
    ),
    Object_Combined_Name
    ),
    {
    Object_Id: Object_Id,
    Object_Name: Object_Name,
    Object_Type: Object_Type,
    Server: Server,
    Database: Database,
    Schema: Schema,
    Object_Combined_Name: Object_Combined_Name,
    Obsolete_Code: Obsolete_Code,
    Reported_Code: Reported_Code,
    Notes: Notes,
    Created_Person_Id: Created_Person_Id,
    Created_Ts: Created_Ts,
    Last_Updated_Person_Id: Last_Updated_Person_Id,
    Last_Updated_Ts: Last_Updated_Ts
    }
    ),
    If(
    RootsNoEDW,
    ForAll(
    Sort(
    Search(
    /*-------------------- begin code block to filter objects by edw project -----------------*/
    Filter(
    OBJECT_DETAIL_ROOTS_MISSING_EDW_EQUIVALENT,
    Object_Id in
    // directly linked to a report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id Or// in any part of an object lineage for an object that is directly linked to a report in EDW project
    Object_Id in// Level 1
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // make sure Master Object directly linked to report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level1_Object_Id Or Object_Id in// Level 2
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level2_Object_Id Or Object_Id in// Level 3
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level3_Object_Id Or Object_Id in// Level 4
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level4_Object_Id Or Object_Id in// Level 5
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level5_Object_Id Or Object_Id in// Level 6
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level6_Object_Id Or Object_Id in// Root
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id Or//Object is EDW object mapped to a root object in a lineage for an object directly linked to report in EDW project
    Object_Id in// EDW Mapping
    Filter(
    EDW_TO_ROOT_OBJECT_MAP,
    Root_Source_Object_Id in
    // lineage where master object directly linked to report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id
    ).EDW_Object_Id
    )
    /*-------------------- end code block to filter objects by edw project -----------------*/,
    box_Source_Object_Search.Text,
    "Object_Combined_Name"
    ),
    Object_Combined_Name
    ),
    {
    Object_Id: Object_Id,
    Object_Name: Object_Name,
    Object_Type: Object_Type,
    Server: Server,
    Database: Database,
    Schema: Schema,
    Object_Combined_Name: Object_Combined_Name,
    Obsolete_Code: Obsolete_Code,
    Reported_Code: Reported_Code,
    Notes: Notes,
    Created_Person_Id: Created_Person_Id,
    Created_Ts: Created_Ts,
    Last_Updated_Person_Id: Last_Updated_Person_Id,
    Last_Updated_Ts: Last_Updated_Ts
    }
    ),
    If(
    NoLineages,
    ForAll(
    Sort(
    Search(
    /*-------------------- begin code block to filter objects by edw project -----------------*/
    Filter(
    OBJECT_DETAIL_MISSING_LINEAGES,
    Object_Id in
    // directly linked to a report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id Or// in any part of an object lineage for an object that is directly linked to a report in EDW project
    Object_Id in// Level 1
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // make sure Master Object directly linked to report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level1_Object_Id Or Object_Id in// Level 2
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level2_Object_Id Or Object_Id in// Level 3
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level3_Object_Id Or Object_Id in// Level 4
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level4_Object_Id Or Object_Id in// Level 5
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level5_Object_Id Or Object_Id in// Level 6
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level6_Object_Id Or Object_Id in// Root
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id Or//Object is EDW object mapped to a root object in a lineage for an object directly linked to report in EDW project
    Object_Id in// EDW Mapping
    Filter(
    EDW_TO_ROOT_OBJECT_MAP,
    Root_Source_Object_Id in
    // lineage where master object directly linked to report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id
    ).EDW_Object_Id
    )
    /*-------------------- end code block to filter objects by edw project -----------------*/,
    box_Source_Object_Search.Text,
    "Object_Combined_Name"
    ),
    Object_Combined_Name
    ),
    {
    Object_Id: Object_Id,
    Object_Name: Object_Name,
    Object_Type: Object_Type,
    Server: Server,
    Database: Database,
    Schema: Schema,
    Object_Combined_Name: Object_Combined_Name,
    Obsolete_Code: Obsolete_Code,
    Reported_Code: Reported_Code,
    Notes: Notes,
    Created_Person_Id: Created_Person_Id,
    Created_Ts: Created_Ts,
    Last_Updated_Person_Id: Last_Updated_Person_Id,
    Last_Updated_Ts: Last_Updated_Ts
    }
    ),
    ForAll(
    Sort(
    // search for OBJECT_DETAIL records matching the search bar text on Object_Combined_Name and in EDW project
    Search(
    /*-------------------- begin code block to filter objects by edw project -----------------*/
    Filter(
    OBJECT_DETAIL,
    Object_Id in
    // directly linked to a report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id Or// in any part of an object lineage for an object that is directly linked to a report in EDW project
    Object_Id in// Level 1
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // make sure Master Object directly linked to report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level1_Object_Id Or Object_Id in// Level 2
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level2_Object_Id Or Object_Id in// Level 3
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level3_Object_Id Or Object_Id in// Level 4
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level4_Object_Id Or Object_Id in// Level 5
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level5_Object_Id Or Object_Id in// Level 6
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level6_Object_Id Or Object_Id in// Root
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id Or//Object is EDW object mapped to a root object in a lineage for an object directly linked to report in EDW project
    Object_Id in// EDW Mapping
    Filter(
    EDW_TO_ROOT_OBJECT_MAP,
    Root_Source_Object_Id in
    // lineage where master object directly linked to report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id
    ).EDW_Object_Id
    )
    /*-------------------- end code block to filter objects by edw project -----------------*/,
    box_Source_Object_Search.Text,
    "Object_Combined_Name"
    ),
    Object_Combined_Name
    ),
    {
    Object_Id: Object_Id,
    Object_Name: Object_Name,
    Object_Type: Object_Type,
    Server: Server,
    Database: Database,
    Schema: Schema,
    Object_Combined_Name: Object_Combined_Name,
    Obsolete_Code: Obsolete_Code,
    Reported_Code: Reported_Code,
    Notes: Notes,
    Created_Person_Id: Created_Person_Id,
    Created_Ts: Created_Ts,
    Last_Updated_Person_Id: Last_Updated_Person_Id,
    Last_Updated_Ts: Last_Updated_Ts
    }
    )
    )
    )
    )
    )
    },
    // iterate through each generated number
    ForAll(
    // genereates a sequence of numbers in the range of the number of Objects
    Sequence(CountRows(Objects)),
    // add a RowNo to each Object record - this allows for dynamic, alternate colors in the gallery based on RowNo
    Patch(
    Last(
    FirstN(
    Objects,
    Value
    )
    ),
    {RowNo: Value}
    )
    )
    )

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,508

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,839

Leaderboard