Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Works great thanks so much!
Hi @lagacem
Hope this solves you're problem. Excel and Power BI are very similar, I've used if condition as a calculated column.
You can also use "Earlier" function for the same.
Thanks,
Ankit
www.linkedin.com/in/ankit-kukreja1904
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
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
39 | |
25 | |
21 |