Get Count of Rows for Table Visual Filtered by Measure

[daniel_digi]

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!

[Anonymous]

Hello! Is there a way I can apply this solution but filtering measures? I've tried replicating it but it doesn't work for me because I don't want to filter a table by a measure, but to filter a measure by another measure.

[amitchandak]

@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])

[daniel_digi]

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.  

[amitchandak · Accepted solution]

@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])

[daniel_digi]

@amitchandak,

Your solution worked with 1 edit. I changed the > 0 to = 0.

Group Count = countx(filter(summarize(Groups, Groups[group code],"_1",COUNTX(FILTER('Activities', Activities[Status] = "Completed"), 'Activities'[ActivityID]) + 0),[_1]=0),[group code])

 This gave me the 169 result I expected, and changes correctly when I update the date slicer.

Thank you very much for your assitance!