cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Natalia_ Regular Visitor
Regular Visitor

Count number of rows where filter is 1

Hello All,

 

Could you please help to calculate number of rows where filter is 1? I've put this formula but there are wrong calculation:

 

Count lines undeliv = CALCULATE(COUNTROWS(CDP_data);FILTER('CDP_data';'CDP_data'[Negative))
 
Many thanks for your help.
6 REPLIES 6
Super User
Super User

Re: Count number of rows where filter is 1

Hi @Natalia_ 

How do you tell it's not delivered? CAn you show a sample of your table?Try something like this  

Count lines undeliv =
CALCULATE (
    COUNTROWS ( CDP_data );
    FILTER ( 'CDP_data'; 'CDP_data'[Undelivered] = 1 )
)
Natalia_ Regular Visitor
Regular Visitor

Re: Count number of rows where filter is 1

Hi @AlB not delivered i've counted like that:  Negative = if(CDP_data[Undelivered]<0;1;0)

 

Super User
Super User

Re: Count number of rows where filter is 1

Ok, so then this should work, as a measure in a card visual. If it doesn't, please share a sample of your tables or the pbix:

Count lines undeliv =
CALCULATE (
    COUNTROWS ( CDP_data );
    FILTER ( 'CDP_data'; 'CDP_data'[Undelivered] < 0  )
)

 

Natalia_ Regular Visitor
Regular Visitor

Re: Count number of rows where filter is 1

@AlB It's working but calculcation is wrong.

I have columnt oredered, delivered, undelivered which is calculated: 

Undelivered = if(CDP_data[Delivered qty]<=CDP_data[Ordered qty];CDP_data[Delivered qty]-CDP_data[Ordered qty];0)+0
And after negative:  Negative = if(CDP_data[Undelivered]<0;1;0)
And I need to know hom many negative (1) lines are in total. 

 

 

 mistake_negative.JPG

Community Support Team
Community Support Team

Re: Count number of rows where filter is 1

Hi @Natalia_ ,

Create a measure as below.

countrows =  CALCULATE (
    COUNTROWS ( CDP_data );
    FILTER ( 'CDP_data'; 'CDP_data'[Negative] =1  )
)
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Natalia_ Regular Visitor
Regular Visitor

Re: Count number of rows where filter is 1

@v-frfei-msft still the same mistake when I have =1, but it counts correctly when i choose =0. 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,746)