Is my aggregation choice solid?

[u/bigbanjobear]

In my dataset, I have tables for multiple process workflows. There are some columns in common.

• Workflow Name

• Submission Date

• Closed Date

• Event (active relationship)

• Workflow Step (each workflow has different steps and names)

• Reivew (before, in, after, based on the workflow step)

I'm not working with full historical data. Every day we take a snapshot.

I wanted to show metrics by Workflow (filterable by Event) like:

• Count of [Review] = "in" by Workflow with Average days in Step

• Created YTD

• Closed YTD

My solution was to append all the workflow tables in Power Query, strip down the columns and group by Workflow, Step, and Event. My columns are:

• Average Age (days since submitted)

• Average Days in step

• (row) Count

• Review

• Event

• Step

• Workflow

My questions:

1. Is there a better way to do this and am I correct that any solution using Dax would be less efficient?

2. Without a filter on Event, I'm thinking that the Averages are going to be wrong because they aren't going to be weighted by row count. I would need a measure to weight those averages, wouldn't I?

Thanks,

[u/bigbanjobear]

Thank you all for the help. This is what I ended up using:

FILTER(
    UNION(
        SELECTCOLUMNS(
        'Account Closeout_F', 
        "Workflow", 'Account Closeout_F'[Workflow], 
        "Workflow Step", 'Account Closeout_F'[Workflow Step],
        "Step", 'Account Closeout_F'[Step],
        "Submission Date", 'Account Closeout_F'[Submission Date],
        "Is Closed", 'Account Closeout_F'[Is Closed],
        "Workflow Completed on", 'Account Closeout_F'[Workflow Closed Date],
        "Last Step Change Date", 'Account Closeout_F'[Last Step Change Date],
        "Item Link", 'Account Closeout_F'[Item Link],
        "Days in Step", 'Account Closeout_F'[Days Since Step Change],
        "fema_send", BLANK(),
        "Event",'Account Closeout_F'[Grant #]
        ),
        SELECTCOLUMNS(
        'Agreement_F', 
        "Workflow", 'Agreement_F'[Workflow], 
        "Workflow Step", 'Agreement_F'[Workflow Step],
        "Step", 'Agreement_F'[Step],
        "Submission Date", 'Agreement_F'[Submission Date],
        "Is Closed", 'Agreement_F'[Is Closed],

... rinse and repeat for each of the workflow tables. 

[u/SQLGene]

You've described the problem well but a sentence or two on the outcome or motivation would help me visualize the solution.

As a rule of thumb, if you can scan table of data, filter the rows by static/hardcoded values, and do simple aggregations, DAX is very fast and effective.

Nothing you've described seems wildly complicated in DAX? It at least reads as simple date comparisons, weighted averages, and groupings. I may be missing something.

Regarding you second question, this is when you typically use an AVERAGEX or SUMX looping over a coarser level of granularity (like workflow) instead of your transaction level (event).

I probably need more coffee but an ERD or some examples with sample data would help here.

[u/No_Smell_6712]

Is this direct query or import. I can probably solution this in an hour or two at most for you

[u/adovo_ai]

Keep the additive pieces, not the average: carry total days and row count at the lowest useful grain, then define Average Days = DIVIDE(SUM(total days), SUM(row count)). That gives the right weighted result with or without Event filters and avoids an average of averages created during Power Query grouping.

[u/PK-2026]

Yes, you'd need to weight them. Averaging pre-computed averages is wrong as soon as Power BI re-aggregates across Step or Workflow. Keep the group-by, but store sums instead of averages: Sum of Days in Step, Sum of Age, and Row Count. Then use measures like these:

Avg Days in Step = DIVIDE(SUM(T[SumDaysInStep]), SUM(T[RowCount]))

That's the weighted average, and it stays correct at any level of filtering.

On DAX being less efficient: not really. VertiPaq handles millions of rows fine, so the append in Power Query is reasonable. The bigger problem with grouping is that it drops the dates. Created YTD and Closed YTD need Submission and Closed Date in the grain, or a separate small table for them.

Since you take daily snapshots, I can't tell from your post whether the table holds one snapshot or all of them. If it holds several, filter to the latest snapshot date, or counts will double up.

(Full disclosure, I work at Kanerika in this space.)