Counting a conditional flag within a group over a subgroup in Power BI

Question

I am looking for help in calculating a total by month, that counts how many applications met an aggregated criteria within that month. A simplified version of the data is that we receive applications, and complete a number of tasks to process that application. Each task has a duration, and adding all the tasks for an application gives the total processing time for the application. We then want to know how many applications were processed in 10 days or less.

Note - the tasks to sum can vary, so we want to calculate the application total duration at the DAX layer, rather than in PowerQuery.

Example raw data:

Application	Task	Period	Duration
A	2859	Aug-22	2
A	2860	Aug-22	2
A	2861	Aug-22	1
B	1990	Aug-22	1
B	1991	Aug-22	20
C	9940	Sep-22	0
C	9941	Sep-22	27
D	1891	Sep-22	1
D	1892	Sep-22	8
E	3697	Sep-22	-
E	3698	Sep-22	26

The calculation condition would then look like this (we don't need to create this view, I'm just including it to explain the logic):

The actual output we're looking to create is a total by month (based on when application was received):

I've got a version of this working by creating a summary table in PowerQuery that makes a unique list of Application and Period combinations, and then links back to the main table to sum the duration for each, but I feel like it should be possible to do this using a DAX formula - as much for my own learning as anything else. Any suggestions on a) how to do it directly in DAX and b) whether this is sensible would be greatly appreciated!

Answer 1

You'll have to adjust the table and column names, but you can accomplish this logic with the following measures. The variable applicationsLessThan10 is a virtual table by Month and application and total duration for each. This is then filtered accordingly and we return the number of rows.

I split out month name into a new column with powerquery - ideally, you would have a date table which would account for year and month and the sorting of the month name.

LessThan10Days =
VAR applicationsLessThan10 =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                ApplicationProcessing,
                ApplicationProcessing[Month Name],
                ApplicationProcessing[Application]
            ),
            "Days", CALCULATE ( SUM ( ApplicationProcessing[Duration] ) )
        ),
        [Days] <= 10
    )
RETURN
    COUNTROWS ( applicationsLessThan10 )


MoreThan10Days =
VAR applicationsMoreThan10 =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                ApplicationProcessing,
                ApplicationProcessing[Month Name],
                ApplicationProcessing[Application]
            ),
            "Days", CALCULATE ( SUM ( ApplicationProcessing[Duration] ) )
        ),
        [Days] > 10
    )
RETURN
    COUNTROWS ( applicationsMoreThan10 )