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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ewelinag17
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

@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
V-pazhen-msft
Community Support
Community Support

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

 

 

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

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

@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

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors