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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cgambino
Frequent Visitor

Count of rows after filtering by a measure

Hello,

 

I'm wondering how I could get the number of rows remaining after filtering.  I am currently using DirectQuery, and my filter is a measure using a DAX statement.

 

For example I have a table like so:

 

Name, Sales, Returns, PercentOfReturnedSales (measure)

 

George: 10, 1, 10%

Sam: 10, 5, 50%

Susan: 100, 5, 5%

Austin: 100, 85, 85%

Ron: 100, 1,1%

Alex: 100, 35, 35%

 

I have a filter that says "only show people who's percent of returned sales is over 15%"

 

So, in this example, it is 3 people. So I want it to show 3.

 

How would this be possible?

1 ACCEPTED SOLUTION
cosborn1231
Resolver I
Resolver I

Hi cgambino,

I hope this helps!

 

Measure = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER('Table','Table'[PercentOfReturnedSales]>15))

View solution in original post

6 REPLIES 6
Benyam991
New Member

Thanks.

 

This Measure works perfectly. However, since some of my responses are "0", it is giving me "(Blank)". How can I update it to give "0" instead of "Blank".

 

I thank you.

twahl
Frequent Visitor

Dose this function apply a grouping?  what I'm trying to do is group by a value to obtain a count of that value with applied filters. 

 

.  

Sql example. 

 

select column1, count(*) [#of records]

from table1

where column2 in ('filter1','filter2')

group by colum1

 

Thanks, 

tom

 

cosborn1231
Resolver I
Resolver I

Hi cgambino,

I hope this helps!

 

Measure = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER('Table','Table'[PercentOfReturnedSales]>15))

you helped me out today with your solution above !!!

Hi cosborn1231,

 

Thanks..it really helps me. But can you suggest how to get 0 (zero) if no data is found in the above query. 

Hi,

Rather new to this myself. But I believe you can get what you want with:

Measure=IF(CALCULATE(DISTINCTCOUNT('Admin Clients'[clientid]),FILTER('Admin Clients','Admin Clients'[id]>15)) = BLANK(),0, CALCULATE(DISTINCTCOUNT('Admin Clients'[clientid]),FILTER('Admin Clients','Admin Clients'[id]>15)))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.