Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I am trying to create a measure that counts the number of cases open given a date.
I tried typing up this measure. I have date table and is has an active relationship with order date and inactive with date delivered. The idea is the find out the count of non-closed cases any point in time.
The measure below doesn’t work the way the way I want it because it doesn’t seem to capture ALL the cases that fall under the filter condition.
Headcount =
var selected = SELECTEDVALUE('Date'[Date])
return
CALCULATE(COUNTA('Cases'[ID]), USERELATIONSHIP('Date'[Date],'Cases'[Date Delivered]), AND('Cases'[Order Date] <= selected, OR(ISBLANK('Cases'[Date Delivered]), selected <'Cases'[Date Delivered]))) + 0
ID | Order Date | Item | Date Delivered |
1 | 6/3/2019 | 2 | 3/26/2020 |
2 | 12/7/2020 | 3 | 3/24/2022 |
3 | 3/19/2018 | 4 | 3/25/2020 |
4 | 8/24/2020 | 9 | 3/20/2021 |
5 | 1/25/2021 | 6 | |
6 | 9/8/2020 | 1 | 3/19/2021 |
7 | 8/2/2019 | 3 | 3/5/2020 |
Solved! Go to Solution.
Open case =
var maxDate = MAX( 'Date'[Date])
return CALCULATE( COUNTROWS(Cases), REMOVEFILTERS('Date'),
Cases[Order date] <= maxDate &&
(Cases[Delivered date] > maxDate || ISBLANK(Cases[Delivered date])
)
Open case =
var maxDate = MAX( 'Date'[Date])
return CALCULATE( COUNTROWS(Cases), REMOVEFILTERS('Date'),
Cases[Order date] <= maxDate &&
(Cases[Delivered date] > maxDate || ISBLANK(Cases[Delivered date])
)
Hello @johnt75
Thanks for the solution, but then number looks off when I used it. And I think it check/test it with a table visual because I guess of the RemoveFilters() function. Is there an alternate solution?
EDIT: Apologies, I double checked. It gave me the correct number. I'll accept your solution.
User | Count |
---|---|
81 | |
76 | |
62 | |
60 | |
48 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |