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 coun...
  • 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)

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