Summarizing like data across multiple tables into a single report

[gemcityzach]

Hey folks,

I'm trying to come up with a way to aggregate and summarize records from multiple SharePoint lists into a single set of reports. Each list has a collection of columns that are from a sub-process that seeks to identify if each record has some 'special' features that needs further investigation. I.e., there are distinct business processes that at some point in time in the process, a set of questions and prompts have to be answered. It's the same questions and prompts across all of these business processes, though the column names are are different, they collect the same types of data. Other than the fact that the sub-process data collection is the same across all of these, the rest of the business processes are distinct and share only commonality in organizational hierarchies and the fact that they all share this 'sub-process' requirement.

The business who designed this sub-process thought it would be a 'better user experience' to have the user have to enter these questions in each of the primary processes as opposed to doing it all centrally in a single SharePoint process/list. This is even though we told them we could automatically generate the record from the primary process into the subprocess using PowerAutomate to give a seamless user experience with minimal duplication. So, the number of processes we continue to build, then the more complex the reporting will likely get as we're just stacking/appending table queries.

What is the most performant way you can think of to achieve my goal of being able to do count total records (tbl1 + tbl2 + tbl3, etc) and to be able to do other time series aggregations like DATEDIFFs on Created Dates vs Identified Dates, etc. Basically, I need to be able to quantify every piece of data across all of this primary processes and their sub processes. Some aggregations:

Total Records across all tables

Total Records within individual tables

Total Records across all table that are Open / Closed

Total Records within individual tables that are Open / Closed

Total records that across all tables that are Past Due

Total records that across all tables that are Out of SLA

Total records that across individuial tables that are Past Due

Total records that across individual tables that are Out of SLA

Records from all tables due in 30 days, 60 days, 90 days

Records from individual tables due in 30 / 60/ 90

Average Age of all records in an Open Status

Average Age of all records in a Closed Status

Average Age of all Records

I need to plot in a stacked bar chart using Time Intelligence (time filtering) Total Items Opened and Total Items Closed

And ALL of this needs to be able to be differentiated by BU1 or BU2, which is Organizational Hierachy data

I've included at least three examples of the data I'm working with. These are just sample columns, but assume they all have different column names to represent roughly the same data being extracted from this common sub-process they share.

tblSrc

created

some_tracking_id

event (list)

identified_dt

decision_dt

closed_dt

decision

past_due_flag

out_of_SLA

boolean1

boolean2

boolean3

boolean4

concat_value1

concat_value2

bu1

bu2

tbl1

1/1/2024

123

a;b;c

12/15/2023

1/31/2024

1/31/2024

some_string

No

no

Yes

No

Yes

No

1234;55555;666666

7777;7771

CSuite1

CSuite1-1

tbl1

1/1/2024

1234

a;b;c

12/18/2023

1/31/2024

1/31/2024

some_string

No

no

No

No

No

No

1234;55555;666666

8833

Csuite2

CSuite1-2

tbl1

1/1/2024

12345

a;b;c;d;

12/20/2023

1/31/2024

1/31/2024

some_string

No

no

Yes

No

No

No

1234;55555;666666

Csuite3

CSuite1-3

tbl1

1/1/2024

123456

a;b;

12/5/2023

1/31/2024

1/31/2024

some_string

No

no

Yes

Yes

Yes

No

1234;55555;666666

15555

CSuite4

CSuite1-4

tbl1

1/1/2024

1234567

a;c;f

12/7/2023

1/31/2024

2/5/2024

some_string

No

no

Yes

Yes

Yes

No

2222

15;30

CSuite5

CSuite1-5

tblSrc

created

a_diff_tracking_id

identified_dt

decision_dt

closed_dt

decision

past_due_flag

out_of_SLA

boolean1

boolean2

boolean3

boolean4

concat_value1

concat_value2

bu1

bu2

tbl2

1/2/2024

init-123

12/16/2023

1/31/2024

1/31/2024

some_string

No

no

Yes

No

Yes

No

1234;55555;666666

7777;7771

CSuite1

CSuite1-1

tbl2

1/2/2024

init-1234

12/19/2023

1/31/2024

1/31/2024

some_string

No

no

No

No

No

No

1234;55555;666666

8833

Csuite2

CSuite1-2

tbl2

1/2/2024

init-12345

12/20/2023

1/31/2024

1/31/2024

some_string

No

no

Yes

No

No

No

1234;55555;666666

Csuite3

CSuite1-3

tbl2

1/2/2024

init-123456

12/15/2023

1/31/2024

1/31/2024

some_string

No

no

Yes

Yes

Yes

No

1234;55555;666666

15555

CSuite4

CSuite1-4

tbl2

1/2/2024

init-1234567

12/17/2023

1/31/2024

2/5/2024

some_string

No

no

Yes

Yes

Yes

No

1234;55555;666666

15555

CSuite5

CSuite1-5

tblSrc

created

another_diff_tracking_id

identified_dt

decision_dt

closed_dt

decision

past_due_flag

out_of_SLA

boolean1

boolean2

boolean3

boolean4

concat_value1

concat_value2

bu1

bu2

tbl3

1/2/2024

tp-123

12/16/2023

1/31/2024

1/31/2024

some_string

No

no

Yes

No

Yes

No

1234;55555;666666

7777;7771

CSuite1

CSuite1-1

tbl3

1/2/2024

tp-1234

12/19/2023

1/31/2024

1/31/2024

some_string

No

no

No

No

No

No

1234;55555;666666

8833

Csuite2

CSuite1-2

tbl3

1/2/2024

tp-12345

12/20/2023

No

no

Yes

No

No

No

1234;55555;666666

Csuite3

CSuite1-3

tbl3

1/2/2024

tp-123456

12/15/2023

1/31/2024

1/31/2024

some_string

No

no

Yes

Yes

Yes

No

1234;55555;666666

15555

CSuite4

CSuite1-4

tbl3

1/2/2024

tp-1234567

12/17/2023

1/31/2024

2/5/2024

some_string

No

no

Yes

Yes

Yes

No

1234;55555;666666

3333;99999

CSuite5

CSuite1-5

I did some testing using Append and I can append all of the tables together, after cleansing them of course. And then did some data cards. I've also incldued some examples of my current visuals just for Tbl1 data. These are what my measures look like just to get total count of records for two tables in a test of append or cross-query:

//Measure within the appended table

totalRecords = CALCULATE (

    COUNTROWS ( 'append' )

    , FILTER ( 'append', [init-tracking_id] <> ""))

     //end initiatives count

    + // add next table records

    CALCULATE(

        COUNTROWS( 'append' )

        , FILTER( 'append', [re-Tracking_ID] <> "")

    ) //end risk event count

 //Measure within a custom table "Key Measures"

totalRecordsKM = CALCULATE( //querying teach table individually

    COUNTROWS( 'initiatives_items' )

    , initiatives[init-tracking_id] <> ""

    )

    +

    CALCULATE(

        COUNTROWS( trigger_event_items )

        , events[re-Tracking_ID] <> ""

        )

The DAX queries from the Append took about 1 ms whereas the dual query took 7 ms. Meaningless at this level and I doubt we'd ever have more than 10,000 records in the LIFETIME of this. I'm just trying to find the easiest and most performant way to make this work so I can get all of my aggregations and visualizations together with the least amount of hassle.

[v-jingzhan-msft]

Hi @gemcityzach 

Appending similar data into an "Append" query and creating measures based on this query would be more efficient. This can help you avoid creating many duplicated measures based on different tables. From your test, you already found that the DAX queries from the Append took less time than the dual query. 10,000 records in a query is not a problem. Power BI is able to deal with this data size quickly. By adding tblSrc column to a slicer or a filter, we can use the slicer/filter to switch viewing data of all tables or a specific table. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[gemcityzach]

Thanks Jing. If I use this append table, then all of my measures will need to account for the multitude of table-column 'relationships'? I.e., if I want to get a count of total records, it'll have to be on all of the particular columns of each segment of the appended table? E.g., tbl1_specific_col + tbl2_specific_col + tbl3_specific_col and if I need filtering then I'll have to ensure that I apply any conditional logic specific to each filterable table-column resource that I want included. I.e., if I only need a measure for tbl1 and tbl2 data, then I have to make sure I'm specific to pick columns and filter variables that only affect the columns and rows from those particular fields?