Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
daniel_digi
New Member

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!

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

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
Super User
Super User

@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.
PBIDesktop_RQS4BeNYNp.png

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

@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!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.