Forum Discussion
Measure combining filter and group by (or similar function)
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
Super UserHi,
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.
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.
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.