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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
MrFahrenheit
Helper I
Helper I

Count Rows Based On Previous Row's value.

I have one column and it has 0s and 1s in it. I ONLY want to count the 0s which are followed by a 1 in the next row. For example, 0,1,0,1,0,1 would count 3 x 0s. Another example, 0,0,0,0,1 would only count one 0 because only one 0 is followed by a 1.

 

1

0

0

1

 

= 1 x 0 (only one 0 is followed by a 1

 

0

1

0

1

1

0

0

= 2 x 0

 

If anyone knows how to do this, please let me know. I checked into all the "Count" expressions but couldn't figure it out. 

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@MrFahrenheit 

First, add an index column to your table in Power Query:

Fowmy_0-1660676038201.png


Then create the following measure to calculate the count:

Zero Count = 
    SUMX(
        Table4,
        VAR __CURRENTINDEX = Table4[Index]
        VAR __NEXTINDEX = __CURRENTINDEX + 1
        VAR __CURRENTDIGIT = Table4[Digit]        
        VAR __NEXTDIGIST = CALCULATE( MAX(Table4[Digit]) , Table4[Index] = __NEXTINDEX , REMOVEFILTERS(Table4))
        RETURN
        INT(__CURRENTDIGIT=0 && __NEXTDIGIST = 1)
    )



 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
Fowmy
Super User
Super User

@MrFahrenheit 

First, add an index column to your table in Power Query:

Fowmy_0-1660676038201.png


Then create the following measure to calculate the count:

Zero Count = 
    SUMX(
        Table4,
        VAR __CURRENTINDEX = Table4[Index]
        VAR __NEXTINDEX = __CURRENTINDEX + 1
        VAR __CURRENTDIGIT = Table4[Digit]        
        VAR __NEXTDIGIST = CALCULATE( MAX(Table4[Digit]) , Table4[Index] = __NEXTINDEX , REMOVEFILTERS(Table4))
        RETURN
        INT(__CURRENTDIGIT=0 && __NEXTDIGIST = 1)
    )



 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thank you for your solution! I tested your suggestion by creating a smaller spreadsheet with only 50 items and manually counted how many "zeros" were followed in the next row by numbers greater than "zero". When I applied your measure/index idea, it came out to the right number!! I modified your code slightly to use "> 0" instead of "+ 1" in this part of the code (see example below) to count all "zeros" followed by any number greater than "zero": 

INT(__CURRENTDIGIT=0 && __NEXTDIGIST = 1)

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.