Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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])
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.
@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])
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |