Forum Discussion
Get Count of Rows for Table Visual Filtered by Measure
Hello,
I am having issues getting a row count of results based off a table being filtered by a measure.
Simplified current table layout is:
Table 1: Groups
Table 2: Activities
Each group has a set of activities associated with them. Activities can have a status of completed, open, canceled and have a date field for when they were completed.
My requirement is that I want to get a list of groups that have no completed activities within a certain date range. I have achieved this by creating the following measure;
Completed Activities = COUNTAX(FILTER('Activties', Activties[Status] = "Completed"), 'Activties'[ActivityID]) + 0
I add this to a table visual, and filter to "Completed Activities is 0", along with the ID of the group and I get a list of every group with 0 completed activities for that period.
What I now need is a count of the groups. It appears you cannot change the aggregation of the Total row so all I am able to do is get a sum which equals 0.
Ideally I would like a Card to show the number, however you cannot apply the measure per group so I do not get a correct result.
A similar question was asked here https://community.powerbi.com/t5/Desktop/Count-of-rows-after-filtering-by-a-measure/m-p/64259#M26546 but did not address the grouping and has not been active since 2018.
Any help will be greatly appreciated!
daniel_digi , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
I doubt anything I added to remove date filter.
Add this to table as well Card and check
countx(filter(summarize(Groups, Groups[group code],"_1",COUNTX(FILTER('Activties', Activties[Status] = "Completed"), 'Activties'[ActivityID]) + 0),[_1]>0),[group code])
5 Replies
daniel_digi , Assumed two table are connected and the group has an ID column it can be a name too
Try
countx(filter(summarize(Groups, Groups[ID],"_1",COUNTAX(FILTER('Activties', Activties[Status] = "Completed"), 'Activties'[ActivityID]) + 0),[_1]>0),[ID])
Unfortunately that does not return the expected result. If I add a count column to the table I can see my expected result is 169 however this returns 609.
While this does give me the count it's a bit ridiculous to have to include an entire column of 1s.
Additionally, the measure you provided does not update when I change the date period. Apologies I initially included the above screen shot indicating I was also filtering dates by a slicer but it doesn't appear to have been embedded correctly.
daniel_digi , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
I doubt anything I added to remove date filter.
Add this to table as well Card and check
countx(filter(summarize(Groups, Groups[group code],"_1",COUNTX(FILTER('Activties', Activties[Status] = "Completed"), 'Activties'[ActivityID]) + 0),[_1]>0),[group code])
