Forum Discussion

lagacem's avatar
lagacem
Frequent Visitor
5 years ago
Solved

Count Number of Rows Based on Filter From Current Row

Hello all,

 

Looking for help on this as I can get it to work in Excel but am having a hard time figuring it out in Power BI. What I'm trying to accomplish is to have at the end of each row, a count of how many INC were still open at the time on resolution. So basically where the opened_at_value of other rows < resolved_at_value this row AND resolved_at_value of other rows >resolved_at_value of this row

 

 

In Excel I can simply get it done with COUNTIFS

 

 

I've tried using COUNTX, COUNTROWS with FILTER but it only returns the calculation on that specific row.

 
  • ERD's avatar
    ERD
    5 years ago

    Hi @lagacem,

    You can try the next option:

    #StillOpenedInc = 
VAR currentResolvedDate = SELECTEDVALUE('Table'[Resolved_date])
VAR Result = 
    COUNTROWS(FILTER(
            ALL('Table'),
            currentResolvedDate > 'Table'[Opened_date] &&
            currentResolvedDate < 'Table'[Resolved_date]
        ))
RETURN IF(ISBLANK(Result), 0, Result)

4 Replies

  • ERD's avatar
    ERD
    Community Champion
    5 years ago

    Hi @lagacem,

    You can try the next option:

    #StillOpenedInc = 
VAR currentResolvedDate = SELECTEDVALUE('Table'[Resolved_date])
VAR Result = 
    COUNTROWS(FILTER(
            ALL('Table'),
            currentResolvedDate > 'Table'[Opened_date] &&
            currentResolvedDate < 'Table'[Resolved_date]
        ))
RETURN IF(ISBLANK(Result), 0, Result)

  • AnkitKukreja's avatar
    AnkitKukreja
    Super User
    5 years ago

    Hi lagacem 

     

    Hope this solves you're problem. Excel and Power BI are very similar, I've used if condition as a calculated column.

    Still_Open =
    IF ( Community_Sol[CLOSE_DATE] > Community_Sol[OPEN_DATE] , 0 , IF ( Community_Sol[CLOSE_DATE] = Community_Sol[OPEN_DATE] , 0 , 1 ) )

     

    You can also use "Earlier" function for the same.

     

    Thanks,
    Ankit
    www.linkedin.com/in/ankit-kukreja1904

    • lagacem's avatar
      lagacem
      Frequent Visitor
      5 years ago

      Thank you for your help.

       

      Unfortunately this isn't what I'm looking for. Yours seems to only show if its still open. What I'm looking for is a count of how many INC were opened but not closed when the INC on that row was closed.

       

      In your table, for example, line 2 was closed the 11th. From that we have

      Line 1 as it was closed on the 4th so counts as 0

      Obviously we exclude line 2

      Line 3 was opened the 9th (before the 11th) and not yet closed so that counts as 1

      Line 4 was opened the 10th (before the 11th) and closed the 16th (after the 11th) and was thus still opened when line 2 was closed so it counts as 1

      Line 5, 6 and 7 were alsc closed the 11th so they count as 0

      Line 8 was opened the 12th so after the line 2 was closed so counts as 0

      Line 9 was opened the 15th so after the line 2 was closed so counts as 0

       

      What I would want is to have the value 2 in the new column at the end of line 2 which is the sum from end line listed above. And to repeat this process for each line.

       

      I hope this has helped clarify my request