cancel
Showing results 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

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

@MrFahrenheit

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

@MrFahrenheit

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
Helper I

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

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors