Running/cummulative product of a measure (not a column)

[MJEnnis]

I need to create a graph of a running product (first row * 1, second row * first row, third row * second row * first row, ect.). But the values to be multiplied need to be calculated. The data to be multiplied are proportions, not something like sales.

Usually you can use PRODUCT() or PRODUCTX() to do something like this. 

But I cannot use a calculated column for the underlying values because a calculated column produces static values for each row. The calculations need to be dynamic for my filters to work properly. I need to use a measure, and PRODUCT() and PRODUCTX() do not work on measures...

I have tried to construct a table in a measure, but PRODUCTX() never returns the correct values using this method.

I have also created a measure that accurately calculates the value which must be multiplied by the previous product. As expected MEASURE * PREVIOUS_MEASURE returns the correct product for the first two rows/observations.  But, as expected, this returns erroneous products for all subsequent rows/observations. 

To better illustrate the problem, below are two table vislualizations. The one on the left is produced with the current MEASURE * PREVIOUS_MEASURE in the second column. The one on the right comes from static calculated columns (all data, unfilterable) which I am using temporarily to check the results of the measure. The second column on the righthand table contains the exact values that the MEASURE produces. The third column contains the intended output.

Is there anyway to calculate a running product of a measure (not on a column)??

Essentially, I am looking for a new measue that does the following with the exisiting measure: 

Time to Status	New Measure	Result
0	84.30%	=84.30%
1	99.74% * 84.30%	=84.08%
2	99.75% * 99.74% * 84.30%	=83.87%

Thanks for any suggestions!

[Greg_Deckler · Accepted solution]

@MJEnnis I don't believe that is true at all. I mocked up a simple scenario where I do a PRODUCTX using an existing measure 2 different ways and it works just fine. It's likely that something is going haywire. I'll try to mock this up with what you have provided me. My PBIX is attached below signature.

[Greg_Deckler]

@MJEnnis I think I did something like this once, Kaplan Meier Survival Curves with Power BI | LinkedIn. If not, can you post sample data and your measure formulas?

[MJEnnis]

@Greg_Deckler  Thanks a lot for the reply. 

I actually ran into your post about Kaplan Meier curves when I was trying to debug some earlier DAX code on this same project. I left a comment that it was really cool!

I think my current situation is a little more complex than what you did though. If I recall correctly, you did all the calculations with a calculated table and/or calculated columns. I need dynamic calculations of the proportion remaining so that the filters in my complex model will work. The other problem I have is that I am tracking time to two distinct events simultaneously (where one is a predictor for the other, but that does not matter at this stage)...

The original data looks something like this (with many other columns and the time to status tables being calculations of time to event or censoring).

'Students'

Student ID	Time to Exit Status	Exit Status	Time to L3 Status	L3 Status
1	0	2 At Matriculation	0	2 At Matriculation
2	3	3 On time	3	3 On time
3	55	3 On time	0	3 On time
4	1500	4 Late	500	4 Late
5		1 N/A		1 N/A
6	1300	4 Late	300	3 On time
7	450	5 Not yet	440	4 Late

I created a static table to get a calculation of the proportion remaining--S(t)--at each day. My method for that, I think, was similar to what you had done. It was of course the same conceptually and mathematically, but since I ran into your post later and since my data model has some issues to be resolved, I used quite different code. But basically same result. 

But in order to plot the two survival curves against the same x-axis, I had to create a calculated table like the following: 

'Time to Status'

Student ID	Time to Status	Status	Status Type
1	0	2 At Matriculation	Exit
2	3	3 On time	Exit
3	55	3 On time	Exit
4	1500	4 Late	Exit
5		1 N/A	Exit
6	1300	4 Late	Exit
7	450	5 Not yet	Exit
1	0	2 At Matriculation	L3
2	3	3 On time	L3
3	0	3 On time	L3
4	500	4 Late	L3
5		1 N/A	L3
6	300	3 On time	L3
7	440	4 Late	L9

 
(Note that censoring is determined based on another column not present above, namely a "student status" column. Also note that my filters come from the 'Students' table, but also from other related tables.)

So my strategy was to create two measures: one for time to "Exit Status" and one for time to "L3 Status". 

I have tried to calculate a table within the measure so that I could just use PRODUCTX(), as I do in my static table. But the calculations at every step are incorrect. 

I have also created a measure which accurately calculate the 1-d/n (1 minus those remaining divided by the number of students who can still experience the event). But I cannot think of a way to return a running product on this measure...

I think the first method is the right one. But I am stuck there and feel so much closer with the second method. 

What do you think?

[Greg_Deckler]

@MJEnnis Just throwing this out there because I haven't had a chance to really dig into this. Could you use a disconnected table for your axis and perhaps that might help. You grab the max value from your disconnected table and then do a PRODUCTX(FILTER('Calculated Table',[Time to Status] < __YourMaxVar),[Measure])

Note that "Calculated Table" could be a Table VAR that you include in the measure.

However, pretty certain that the specifics of the solution are going to depend on how you wrote your measures and if they involve CALCULATE that could really be tricky to determine what is going on exactly.

[MJEnnis]

Hi @Greg_Deckler! So I have worked on the measure that relies on a Table Var, and with some tweaking it now returns the right 1-d/n calcuations (same that you see above), using MINX(), MAXX(), or AVERAGEX() to return those vlaues. But when I try to add a column to the Table Var to calculate PRODUCTX() of the 1-d/n values, it returns either the same 1-d/n value or some weird percentage thereof that I can figure out. 

Here is the code I am using, with notes on where it stops working... 

Any ideas??

Table = 
VAR TTS_N = SELECTCOLUMNS(FILTER('Time to Status',
'Time to Status'[Status Type] = "Exit"),
"ST", 'Time to Status'[Status Type],
"TToS", 'Time to Status'[Time to Status],
"StID", 'Time to Status'[Student ID],
"Stat", 'Time to Status'[Status],
"Censd", 'Time to Status'[Censored])


VAR TTS_N1 = ADDCOLUMNS(TTS_N,

"Total", COUNTROWS(FILTER(ALL('Time to Status'), 'Time to Status'[Status Type] = "Exit")),

"Met", COUNTX(FILTER(TTS_N, 
[ST] = "Exit"
&& [Stat] <> "1 N/A"
&& [Stat] <> "5 Not Yet"
&& [TToS] = MAX([TToS])
), [StID]),

"Censored_",  COUNTX(FILTER(TTS_N, 
[ST] = "Exit"
&& [Censd] = "Yes"
&& [TToS] = MAX([TToS])
), [StID]),

"P_D_",  
MAXX(FILTER('Student Language Levels', 
[TToS] > 'Student Language Levels'[Time to Exit Status]), 
'Student Language Levels'[Time to Exit Status]),

"P_D_1",  
MAXX(FILTER('Time to Status', 
[TToS] > 'Time to Status'[Time to Status]
&& 'Time to Status'[Status Type] = "Exit"), 
'Time to Status'[Time to Status])
)


VAR TTS_N2 = ADDCOLUMNS(TTS_N1,
"TOT_CENS", IF(ISBLANK([P_D_]), BLANK(), 

COUNTROWS(FILTER('Student Language Levels',
[TToS] > 'Student Language Levels'[Time to Exit Status] 
&& NOT('Student Language Levels'[Student Status] IN {"Degreed", "Active", "Graduand"}) 
&& 'Student Language Levels'[Exit Status] = "5 Not Yet")
)))


VAR TTS_N3 = ADDCOLUMNS(TTS_N2,
"Remaining_Prev", 

[Total] - IF(ISBLANK([P_D_]), 0, 
COUNTROWS(FILTER('Student Language Levels', [TToS] > 'Student Language Levels'[Time to Exit Status] 
&& NOT('Student Language Levels'[Exit Status] IN {"1 N/A", "5 Not Yet"})
&& NOT(ISBLANK('Student Language Levels'[Time to Exit Status])))))
- [TOT_CENS])

VAR TTS_N4 = ADDCOLUMNS(TTS_N3,
"1-d/n", 1 - Divide([Met],[Remaining_Prev])
)  

###### SO FAR SO GOOD! ######

VAR TTS_N5a1 = SUMMARIZE(TTS_N4,
[TToS],
"P_N_M", PRODUCTX(FILTER(TTS_N4, 
[TToS] <= MAX([TToS])), 
[1-d/n])
)

###### any function of [P_M_M] returns same as [1-d/n] #######

VAR TTS_N5a = DISTINCT(SELECTCOLUMNS(TTS_N4, 
"Status Type1", [ST],
"Time to Status1", [TToS],
"1-d/n1", [1-d/n]))

VAR TTS_N5b = ADDCOLUMNS(TTS_N5a,
"PERC_N_M", PRODUCTX(FILTER(TTS_N5a,
[Time to Status1] <= MAX([Time to Status1])),
[1-d/n1]))


###### any function of [PERC_N_M] returns same as [1-d/n] #######

VAR TTS_N6 = ADDCOLUMNS(TTS_N4,
"Percent_Not_Met_", MAXX(FILTER(TTS_N5b, 
[Status Type1] = [ST]
&& [Time to Status1] = [TToS]),
[PERC_N_M])
)

###### any function of [Percent_Not_Met_] returns same as [1-d/n] #######

RETURN MINX(TTS_N6, [Percent_Not_Met_])

[MJEnnis]

Note that the [Censd] and [Censored] are not necessary for the final calculation. This is because to get a running count of censored "participants", I had to refer back to the original table. [P_D_1] is useless. I still have to do some cleaning.

[MJEnnis]

@Greg_Deckler , I think this is what I have tried, essentially, for the first method. Let me go back and run that code step by step to try and see where the errors in the calculation start, then I can share the code...

So really no way to do a running product on an existing measure, right?

[Greg_Deckler · Accepted solution]

@MJEnnis I don't believe that is true at all. I mocked up a simple scenario where I do a PRODUCTX using an existing measure 2 different ways and it works just fine. It's likely that something is going haywire. I'll try to mock this up with what you have provided me. My PBIX is attached below signature.

[barakb]

@MJEnnis , @Greg_Deckler 

This worked for me:

%_Not_Met = var a=SUMMARIZE(all(Query1),Query1[Days],"1-d/n",[1-d/n])
var b=FILTER(a,[Days]<=max(Query1[Days]))
var c=CALCULATE(PRODUCTX(b,[1-d/n]))
return c

[MJEnnis]

@Greg_Deckler Took a look at your file. Seems like it worked for you by using one measure to return the factors and another to return the cummulative product of the factors. I am currently trying to do everything with one measure. Maybe I am simply missing ALL() to remove filters on the factors? Maybe I need to use a second measure to return the products?

[Greg_Deckler]

@MJEnnis This is a weird construct:

VAR TTS_N5a1 = SUMMARIZE(TTS_N4,
[TToS],
"P_N_M", PRODUCTX(FILTER(TTS_N4, 
[TToS] <= MAX([TToS])), 
[1-d/n])
)

You might be running into funkiness with SUMMARIZE. I would suggest either of the following:

VAR Max_TToS = MAX([TToS])

VAR TTS_N5a1 = 
  SUMMARIZE(
    FILTER(TTS_N4, [TToS] <= Max_TToS),
    [TToS],
    "P_N_M", PRODUCTX(TTS_N4, [1-d/n])
  )

or

VAR Max_TToS = MAX([TToS])

VAR TTS_N5a1 = 
  GROUPBY(
    FILTER(TTS_N4, [TToS] <= Max_TToS),
    [TToS],
    "P_N_M", PRODUCTX(CURRENTGROUP(), [1-d/n])
  )

[MJEnnis]

The SUMMARIZE variable in the code was a test which was not used in the code above. When it was used, it produced the same result as the other option in the code. It always returns the same value as any function of [1-d/n]. I did not try your second method, but the first does the exact same thing. So... what this means is that PRODUCTX() in the context of this measure is only considering the current row and returning 1*[1-d/n]. Even storing [1-d/n] as a variable and then adding it to a new TABLE VAR results in 1*[1-d/n]. So I need to use ALL() or ALLSELECTED to get PRODUCTX() to accumulate up to the current row, which cannot be done with a TABLE VAR. As you propose in your file, maybe the only way to do this is to store [1-d/n] as its own measure and then create a new measure to plug this into the source table, filtered appropriately. I will mess around a bit more and then try it again. But it seems (to me) that the only way to get PRODUCTX() to work properly in this context is the way you did it. Will let you know. Thanks so much for this!

[MJEnnis · Accepted solution]

@Greg_Deckler 

The two measure option works! And the filters seem to work too! I would have prefered one measure to avoid more clutter in my model. But I can simply hide the intermediate measure from report view. 

This is the near final code that produces the intermediate calculations of the factors. There are still a few minor bugs (indicated), and I may try to clean it up a bit more. But it gets the job done for now!

1-d/n_Exit = 

VAR MAX_TToS1 = MAXX(FILTER('Time to Status', 
    'Time to Status'[Status Type] = "Exit"), 
    'Time to Status'[Time to Status])

VAR TOT_STS = COUNTROWS(FILTER(ALLSELECTED('Time to Status'), 
    'Time to Status'[Status Type] = "Exit")) 

###TOT_STS calculates off of a calculated table used to generate a common axis for two distinct but related survival curves. The calculation produces the right values, but for some reason when the final output is plugged into the second measure, the percent remaining calculation is way off.### 

VAR TOT_STS1 = COUNTROWS(FILTER(ALLSELECTED('Student Language Levels'), 
    'Student Language Levels'[Exit Status] <> "1 N/A"))

###TOT_STS1 calculates off of the original table and produces the same values as TOT_STS. For some reason, calculating this value off of the original table works when plugged into the second measure.###

VAR TOT_CENS = COUNTROWS(FILTER('Student Language Levels',
    MAX_TToS1 > 'Student Language Levels'[Time to Exit Status] 
    && NOT('Student Language Levels'[Student Status] IN {"Degreed", "Active", "Graduand"}) 
    && 'Student Language Levels'[Exit Status] = "5 Not Yet")
    )

VAR MET = COUNTROWS(FILTER('Time to Status', 
    'Time to Status'[Status Type] = "Exit"
    && 'Time to Status'[Status] <> "5 Not Yet"
    && 'Time to Status'[Time to Status] = MAX_TToS1))

VAR TOT_MET = COUNTROWS(FILTER('Student Language Levels', 
    MAX_TToS1 > 'Student Language Levels'[Time to Exit Status] 
    && NOT('Student Language Levels'[Exit Status] IN {"1 N/A", "5 Not Yet"})
    && NOT(ISBLANK('Student Language Levels'[Time to Exit Status]))))

VAR REM_PREV = TOT_STS1 - TOT_MET - TOT_CENS

###REM_PREV produces the number of "participants" still remaining at the current time (i.e., at each the beginning of each day/at each row). For rows where TOT_MET and TOT_CENS are blank, it returns the baseline total, rather than the current remaining. This does not matter, because when this value is put into the next variable calculation, 1-0= 1. Which is the value it needs to be anyway. I will try to fix this. ###

VAR O_M_M_O_R = 1 - DIVIDE(MET, REM_PREV)

RETURN O_M_M_O_R

And here is the second measure. 

Percent_not_Met_Exit = 

VAR MAX_TToS_ = MAX('Time to Status'[Time to Status])

    VAR Days_ = DISTINCT(SELECTCOLUMNS(FILTER(ALLSELECTED('Time to Status'), 
    'Time to Status'[Time to Status] <= MAX_TToS_),
    "COLUMN", 'Time to Status'[Time to Status]))

###Using ALLSELECTED() in Days_ may be what is causing the bug mentioned above... Need to experiment. ###

    VAR P_N_M = PRODUCTX(Days_, [1-d/n_Exit])
    
RETURN
    P_N_M

Now I will recycle the code to get the time-to-event data for the secondary curve, and then I can plot both on one axis with correct values and functional filters! The only other detail is that Kaplan-Meier curves treat time as continuous but make no assuptions about what has happened between time points. So, the resulting graph should look sort of piecemeal. I may try to work this out at a later point, but as long as this is known, then it should not be a problem...

Thanks so much @Greg_Deckler ! So rewarding when something like this eventually works out! And it would not have worked out, at least not so soon, without your input...

[Greg_Deckler]

@MJEnnis Sweet! Glad I could help!