cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Counting the same values ​​until encountered other.

Dear,
I need to create the following table in the report.

number.PNG

It is supposed to count all "out" values ​​from each number and count "in" values ​​(from the end each number) until the first "out" is encountered. What should such functions look like?
I am attaching sreen of pbix file.

1.JPG2.JPG
Thank you in advance for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Counting the same values ​​until encountered other.

@ewelinag17 

https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EUco0oiHIxtJmIdYxiAb...

 

table1.JPG

Here is the steps:

1. Add an Index column in Query Editor,

 

2. Add a Rank column

Rank = RANKX(FILTER('Table',[test number]=EARLIER('Table'[test number])),[Index],,DESC)
 
3. Create the measures

Countout = CALCULATE(COUNT('Table'[result]),FILTER('Table',[result]="out"))+0
countintoout = IF([Countout]=0,COUNT('Table'[result]),CALCULATE(MIN('Table'[Rank]),FILTER('Table',[result]="out"))-1)


Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Community Support
Community Support

Re: Counting the same values ​​until encountered other.

@ewelinag17 

Basically you need to add an index and a rank column to identify the position of the "out" value. Then create a measure to find the place of the "out" value.

 

count in to out.JPG

Please check the pbix for detail:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EUco0oiHIxtJmIdYxiAb...


Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Highlighted
Frequent Visitor

Re: Counting the same values ​​until encountered other.

@V-pazhen-msft 

I'm having trouble opening a file from you. Can you insert a file that is not on OneDrive or screenshots of the table and measures.

Highlighted
Community Support
Community Support

Re: Counting the same values ​​until encountered other.

@ewelinag17 

https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EUco0oiHIxtJmIdYxiAb...

 

table1.JPG

Here is the steps:

1. Add an Index column in Query Editor,

 

2. Add a Rank column

Rank = RANKX(FILTER('Table',[test number]=EARLIER('Table'[test number])),[Index],,DESC)
 
3. Create the measures

Countout = CALCULATE(COUNT('Table'[result]),FILTER('Table',[result]="out"))+0
countintoout = IF([Countout]=0,COUNT('Table'[result]),CALCULATE(MIN('Table'[Rank]),FILTER('Table',[result]="out"))-1)


Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: Counting the same values ​​until encountered other.

@V-pazhen-msft 

What measures will look like when empty values ​​appear. The appear at the end of the test numbers.

ewelinag17_0-1597226529234.png

The end result should be as you have obtained.

Przechwytywanie.PNG

Highlighted
Community Support
Community Support

Re: Counting the same values ​​until encountered other.

@ewelinag17 

I guess you could still try the same formulas, because blank is also considered as a row value in power bi and the formula only looks for the "out “value, so ”in“ and ”blanks“ does not really matter.


Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors