Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm writing a measure to calculate the total "active" help desk cases at any point in time.
Cases are "active" between their [EntryDate] and [CloseDate] inclusive.
I want to graph this data over time in a line chart, and I want to adjust the time window using a date slicer.
I have a 'Date' table and a 'Cases' table, with [Date] 1:* [EntryDate].
Cases with an [EntryDate] outside the date window should still be counted if they are active inside the date window. I've accomplished this by using ALL('Cases') in my DAX (see below).
The problem is, I also want to filter the measure by [Dept]. I don't see how I can do this while using ALL('Cases'). How can I solve this problem?
CaseVolume =
VAR vol =
CALCULATE (
COUNT ( 'Cases'[CaseID] ),
FILTER (
ALL ( 'Cases' ),
'Cases'[EntryDate] <= MAX ( 'Date'[Date] )
&& (
'Cases'[CloseDate] >= MAX ( 'Date'[Date] )
|| ISBLANK ( 'Cases'[CloseDate] )
)
)
)
RETURN
IF ( ISBLANK ( vol ), 0, vol )
Case Table:
HI,
Please try using ALLSELECTED DAX function.
ALLSELECTED function (DAX) - DAX | Microsoft Learn
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you for the answer. Unfortunately, there is still an issue.
On 17th May, there were 45 active cases. Using ALLSELECTED, only 19 are counted. There are 26 missing from the total. They are filtered out by the date slicer, because the [EntryDate] is before 17th May...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |