Forum Discussion
Counting the same values until encountered other.
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.
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 measuresCountout = 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.
5 Replies
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:
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.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.
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 measuresCountout = 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.
