topic Count from SUMMARIZE over time in DAX Commands and Tips

Count from SUMMARIZE over time

MAVIE — Thu, 09 Mar 2023 13:39:03 GMT

Hi All,

I'm trying to visualize how many bugs were opened or closed over time.
For this my data consists of a table with BugVersions and their statuses, as well as a date table.

As I only want dates when the bugs were Opened/Closed I want the first version when they have either status "New" or "Done".

In order to do this I have been trying to use SUMMARY to look at the first date with either status, however when this is evaluated in the context of the date table, then all version still apear.
The measure that I have so far looks as such:

For reference the SUMMARY table that I am creating looks as such:

Filtered on one bug, I get the following visual, from which I want to remove all lines, but the first green and first red

Any help would be greatly appreciated

Re: Count from SUMMARIZE over time

MAVIE — Thu, 09 Mar 2023 13:47:56 GMT

I have been able to get the correct result by first creating a calculated table and then creating a count based on that.

However this is not satisfactory, since I cannot use the same slicers for both the calculated table and the FactBugs_T2 table.

Re: Count from SUMMARIZE over time

tamerj1 — Mon, 13 Mar 2023 15:48:31 GMT

MAVIE

Please try

Measure2 =
VAR T =
ALLSELECTED ( 'Date'[Year] )
RETURN
IF (
SELECTEDVALUE ( 'Date'[Year] )
IN { MINX ( T, 'Date'[Year] ), MAXX ( T, 'Date'[Year] ) },
[Measure]
)

Re: Count from SUMMARIZE over time

MAVIE — Mon, 13 Mar 2023 20:32:49 GMT

Hi tamerj1

Thank you for your help.

Unfortunately, the measure only filters the date dimension to the current year, but does not filter the bug versions.

The fix that I am trying to achieve is one that applies a filter on my table FactBugs_T2, such that I only get the earliest version of the "Bug" where it has status "New" and "Done" for each day in my date dimension.

For that reason I have been trying to use the following measure

Measure = VAR BugGroup = CALCULATETABLE( SUMMARIZE ( FactBugs_T2, FactBugs_T2[BugID], FactBugs_T2[BugType], FactBugs_T2[LookupStatusType], "FirstVersion", MIN ( FactBugs_T2[VersionCreated] ) ), FactBugs_T2[LookupStatusType] IN {"New","Done"} ) RETURN COUNTROWS ( BugGroup )

This does not seem to apply the filters to FactBugs_T2[VersionCreated] in the right context.

If I first create a calculated table with same code as in VAR BugGroup, and then creates a measure to COUNTROWS on that, then I get the correct result, however then other slicers from my report do not work with the measure.

I hope that makes sense.