topic DAX Measure - Graph Active Cases Over Time in DAX Commands and Tips

DAX Measure - Graph Active Cases Over Time

cdfizz17 — Wed, 07 Jun 2023 00:44:42 GMT

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:

Re: DAX Measure - Graph Active Cases Over Time

Jihwan_Kim — Wed, 07 Jun 2023 03:19:08 GMT

HI,

Please try using ALLSELECTED DAX function.

ALLSELECTED function (DAX) - DAX | Microsoft Learn

Re: DAX Measure - Graph Active Cases Over Time

cdfizz17 — Wed, 07 Jun 2023 04:12:55 GMT

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...