Measure combining filter and group by (or similar function)

[BartvDonkelaar]

Hello,

I have a table containing the following columns:

- datetime

- ID

- eventname

- delta time between datetime and now (calculated column in PowerQuery)

Example:

datetime	ID	eventname	DaysAgo
13-7-2022 07:21	1234	Incomplete1	117
13-7-2022 07:21	1234	Incomplete1	117
13-7-2022 07:21	1234	Incomplete1	117
13-7-2022 07:21	1234	Incomplete1	117
15-7-2022 07:21	1234	Incomplete1	115
16-7-2022 07:21	1234	Incomplete1	114
17-7-2022 07:21	1234	Incomplete1	113
4-8-2022 07:21	1234	Incomplete1	95
4-8-2022 14:21	1234	Incomplete1	95
5-8-2022 07:21	1234	Incomplete1	94
6-8-2022 07:21	1234	Incomplete1	93
7-8-2022 07:21	1234	Incomplete1	92
20-9-2022 07:21	1234	Incomplete1	48
14-7-2022 07:21	1235	Incomplete1	116
14-7-2022 07:21	1235	Incomplete1	116
14-7-2022 07:21	1235	Incomplete1	116
14-7-2022 07:21	1235	Incomplete1	116
10-7-2022 07:21	4923	Incomplete2	120
10-7-2022 07:21	4925	Incomplete2	120
11-7-2022 07:21	4925	Incomplete1	119
11-7-2022 07:21	4925	Incomplete1	119
12-7-2022 07:21	5825	Incomplete1	118
12-7-2022 07:21	5825	Incomplete1	118
12-7-2022 07:21	5925	Incomplete1	118
12-7-2022 07:21	5925	Incomplete1	118
1-7-2022 05:09	5947	Slow2	129
1-7-2022 03:58	7923	Slow1	129
1-7-2022 04:02	7923	Incomplete1	129
1-7-2022 04:41	7923	Slow1	129
1-7-2022 04:41	7923	Incomplete1	129
1-7-2022 12:25	7923	Slow1	129
1-7-2022 13:48	7923	Incomplete1	129
1-7-2022 13:48	7923	Slow1	129
1-7-2022 13:48	7923	Incomplete1	129
1-7-2022 23:48	7923	Incomplete1	128
2-7-2022 01:22	7923	Slow1	128
8-7-2022 03:59	7923	Slow1	122
8-7-2022 13:30	7923	Slow1	122
8-7-2022 13:36	7923	Incomplete1	122
10-7-2022 07:22	7923	Slow1	120
10-7-2022 08:08	7923	Incomplete1	120
10-7-2022 18:51	7923	Slow1	120
10-7-2022 20:30	7923	Incomplete1	119

When creating a table visual in PowerBI I can create a table like the following:

ID	Incomplete1
1234	13

Here I applied the following filters:

- DaysAgo >= 120

- Event = "Incomplete1"

- Count of events with same ID >= 10

What I want to accomplish is to perform these filters in a measure. I have searched for the correct funtions, but cannot figure it out. Using 'Filter' I can filter on 'DaysAgo' and on the name of 'Event'. What I cannot get working is to group on ID and show only the ID's that occur 10 or more times.

Could someone help me out?

Kind regards,

Bart

[Ashish_Mathur]

Hi,

At the ID level, how does one have to calculate the Days ago?  Do we have to add the 13 individual entries (after filtering for incomplete1) in the days ago column of ID 1234 or do we have to add the 10 individual unique entries (after filtering for incomplete1) in the days ago column of ID 1234?  Please clarify.

[BartvDonkelaar]

Hi @Ashish_Mathur ,

Thank you for your reply!

I'd like to add all entries for the ID's that meet the filter criteria.

I see that my example data is incomplete, as there should be seconds information in the datetime field. That addition makes all entries unique.

Would that answer your question?

Kind regards,

Bart

[Ashish_Mathur]

Hi,

Does this measure work?

Count = countrows(Data)

Measure = COUNTROWS(FILTER(VALUES(Data[ID]),[DaysAgo]>=120&&[Event]="Incomplete1"&&[count]>=10))

Hope this helps.

[BartvDonkelaar · Accepted solution]

Hi @Ashish_Mathur ,

When I try this measure, I get an error when entering the FilterExpression. Here 

[DaysAgo]>=120&&[Event]="Incomplete1"&&[count]>=10

Is not recognized. The message 'Cannot find name ...' appears.

COUNTROWS(
    FILTER(
        VALUES(
            table[ID]
        ),
        table[DaysAgo] <=120 &&
        table[eventname]="Incomplete1" &&
        COUNTROWS(
            table
        ) >= 10
    )
)

I think this is because the function 'VALUES' within the filter only relates to the [ID] column of the table, so this is the only table that is recognized to use in the FilterExpression.

What I tried myself is the following:

1. Filtering for DaysAgo and eventname is succesful.

2. Grouping by ID (using SUMMARIZE or SUMMARIZECOLUMNS) is also succesfull, only when I start with this step the DaysAgo information is lost because of the grouping

3. When applying the filter first, I cannot seem to refer to the newly generated filtered table.

This got me thinking.. and instead of generating a measure for the filters I generated a new table. Here I applied the same logic as in the measure, only resulting in a table. This table as turned out could be used in the SUMMARIZECOLUMNS function. So now I have my column with filters and grouping! 🙂

Final filter table:

_Incompmlete1Filter = 
FILTER(
    table,
    table[eventname] = "Incompmlete1" &&
    table[DaysAgo] <= 120
)

 Final count table:

_Incomplete1Count = 
SUMMARIZECOLUMNS(
    _Incompmlete1Filter[ID],
    _Incompmlete1Filter[eventname],
    "Count",
    COUNT(
        _Incompmlete1Filter[eventname]
    )
)

Thanks for thinking along!

Kind regards,

Bart

[Ashish_Mathur]

Since your reply has been marked as Answer, my guess is that is question is answered.

[Greg_Deckler]

@BartvDonkelaar With ID in your table visual, try something like:

Measure = 
  VAR __Table = FILTER('Table', [DaysAgo] >= 120 && [Event] = "Incomplete1")
  VAR __Count = COUNTROWS(__Table)
  VAR __Result = IF(__Count >= 10, 1, 0)
RETURN
  __Result

Basically a Complex Selector: The Complex Selector - Microsoft Power BI Community

[BartvDonkelaar]

Hi @Greg_Deckler ,

Thank you for your reply!

Your solution does not however incorporate the ID.

I am only interested in a case when for any individual ID the count is 10 or higher. So 10 or more times 1234 for example. In your example you sum over the total count, which does not use ID.

Kind regards,

Bart