Count Number of Rows Based on Filter From Current Row

[lagacem]

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.

[AnkitKukreja]

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]

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

[ERD · Accepted solution]

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)

[lagacem]

Works great thanks so much!