topic Re: Count Number of Rows Based on Filter From Current Row in DAX Commands and Tips

Count Number of Rows Based on Filter From Current Row

lagacem — Wed, 17 Mar 2021 13:45:20 GMT

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.

Re: Count Number of Rows Based on Filter From Current Row

AnkitKukreja — Wed, 17 Mar 2021 16:31:05 GMT

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

Re: Count Number of Rows Based on Filter From Current Row

lagacem — Wed, 17 Mar 2021 20:21:04 GMT

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

Re: Count Number of Rows Based on Filter From Current Row

ERD — Wed, 17 Mar 2021 20:58:00 GMT

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)

Re: Count Number of Rows Based on Filter From Current Row

lagacem — Thu, 18 Mar 2021 18:52:37 GMT

Works great thanks so much!