Counting the same values ​​until encountered other.

[ewelinag17]

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

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.

Thank you in advance for your help.

[Anonymous]

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

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.

[ewelinag17]

@Anonymous 

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.

[Anonymous · Accepted solution]

@ewelinag17 

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

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.

[ewelinag17]

@Anonymous 

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

The end result should be as you have obtained.

[Anonymous]

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