cumulative count within DAX table variable

[scotjn1]

I have been asked for a measure to give the average number of observations of X that have occurred between observations of Y within various samples.  To do this, I'm using a DAX table variable within the measure to structure the data for counting.  I just cannot figure out how to do the "lookback" in order to do a cumulative count.

Here is an example of what I have so far within the table variable:

Each 1 in Type1 and Type2 indicates a positive observation.  I need to count the number of Type2 observations between every Type1 observation, inclusive.  I also need to "reset" any counters with changes to the SampleID.  These are the expected results of the cumulative counts:

Any guidance on how to do this is appreciated!

[v-henryk-mstf]

Hi @scotjn1 ,

Create TEMP as a calculated table and then add this column. But the results still seem to be problematic. Can you further explain the judgment condition between type1 and type2, so that I can do further testing.

Looking forward to your feedback.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[scotjn1]

Hi @v-henryk-mstf ,

This is getting very close.  The count of 17 is correct in your screenshot.  Since I only care about the count where Type1 = 1, the intermediate steps are irrelevant so it's OK that they all show the final count of 17 instead of the increments in my original post.

The next step to solve is that the count should "reset" each time Type1 = 1, so in cases where a given SampleID has multiple records where Type1 = 1 the incremental counter would be this:

Here is a SampleID where some of the counts would be zero:

Thank you for solving the first step!

Jacob

[amitchandak]

@scotjn1 , Try a new column as

var _max = minx(filter(table, [sampleID] = earlier([sampleID]) && [Date] >= earlier([Date]) && [Type1] =1), [Date])
return
sumx(filter(table, [sampleID] = earlier([sampleID]) && [Date] <= _max), [Type2])

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[scotjn1]

Hello @amitchandak,

Thank you for the reply.  EARLIER doesn't like that since it "refers to an earlier row context which doesn't exist."  Maybe because it's looking at a temp table?

Here's the .pbix with data and the DAX table in question.

Thank you,

Jacob