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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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/EUco0oiHIxtJmIdYxiAbxN4BInEUSQJ05H-VOMRSpEONTQ?e=f4RDAj


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.