Forum Discussion

ewelinag17's avatar
ewelinag17
Frequent Visitor
6 years ago
Solved

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" ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    ewelinag17 

    https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EUco0oiHIxtJmIdYxiAbxN4BInEUSQJ05H-VOMRSpEONTQ?e=rtIZl0

     

    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's avatar
ewelinag17
Frequent Visitor
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

ewelinag17 

https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EUco0oiHIxtJmIdYxiAbxN4BInEUSQJ05H-VOMRSpEONTQ?e=rtIZl0

 

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's avatar
    ewelinag17
    Frequent Visitor
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.