Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am constructing a “survivor curve” of sorts. Really it is the inverse of a survivor curve in that it is not plotting “survivors” but “non-survivors” over time. Specifically, I am plotting the percentage/proportion of students who have not yet met their language requirements each day since they matriculated at a university.
The reference table contains data for all students. The relevant data is something like this.
Student ID |
Time to Exit Requirement |
Exit Status |
1 |
0 |
2 At Matriculation |
2 |
3 |
3 On time |
3 |
55 |
3 On time |
4 |
1500 |
4 Late |
5 |
|
1 N/A |
6 |
1300 |
4 Late |
7 |
300 |
5 Not yet |
I have successfully created a calculated table that is essentially a distribution of the number of students who met their requirement each day from beginning of studies (0 days) to however long it takes (4000+ days in rare cases). The following code (sans some other columns for statistical analyses) did the trick to get me a static survivor curve:
Survivor Curve =
VAR DAYS = DISTINCT(SELECTCOLUMNS(FILTER('Student Language Levels', NOT(ISBLANK('Student Language Levels'[Time to Exit Status]))),
"Days", 'Student Language Levels'[Time to Exit Status]
))
VAR MET = ADDCOLUMNS(DAYS,
"Met", COUNTROWS(FILTER('Student Language Levels', [Days] = 'Student Language Levels'[Time to Exit Status] && 'Student Language Levels'[Exit Status] <> "5 Not Yet" && 'Student Language Levels'[Exit Status] <> "1 N/A" && NOT(ISBLANK('Student Language Levels'[Time to Exit Status])))))
VAR TOTAL = ADDCOLUMNS(MET,
"Total", Calculate(COUNTROWS('Student Language Levels'), FILTER('Student Language Levels', NOT(ISBLANK('Student Language Levels'[Time to Exit Status])))))
VAR REMAINING_NOW = ADDCOLUMNS(TOTAL,
"Remaining_N", DIVIDE([Total] - COUNTROWS(FILTER('Student Language Levels', [Days] >= 'Student Language Levels'[Time to Exit Status] && 'Student Language Levels'[Exit Status] <> "5 Not Yet" && 'Student Language Levels'[Exit Status] <> "1 N/A" && NOT(ISBLANK('Student Language Levels'[Time to Exit Status])))), [Total]))
RETURN SELECTCOLUMNS(REMAINING_NOW,
"Days", [Days],
"Met", [Met],
"Remaining_N", [Remaining_N])
However, I need the values beling plotted as a measure so that I can apply various filters available in my model. One obvious example is that in need to be able to filter out students with the status of “5 Not yet” in certain cases. But the data can be segmented in many other ways as well… And the filters obvious will not work on my calculated distribution table.
However, when I try using this similar code as a measure, it only gets me part of the way there.
PERCENT_NOT_MET =
Var MET = COUNTROWS(FILTER('Student Language Levels', 'Student Language Levels'[Time to Exit Status] >= 'Student Language Levels'[Time to Exit Status] && 'Student Language Levels'[Exit Status] <> "5 Not Yet" && 'Student Language Levels'[Exit Status] <> "1 N/A" && NOT(ISBLANK('Student Language Levels'[Time to Exit Status]))))
Var STS = COUNTROWS(FILTER(ALL('Student Language Levels'), 'Student Language Levels'[Exit Status] <> "1 N/A" && NOT(ISBLANK('Student Language Levels'[Time to Exit Status]))))
Var PERCENT_NOT_MET = DIVIDE(STS-MET, STS)
RETURN PERCENT_NOT_MET
Rather than summing the running totals (including all previous days) for each new day, it only returns the daily total. This is because the calculate table includes a “days” column, whereas the measure returns row-by-row results for “time to exit requirements”. It is completely ignoring the ">=". And so when I plot the measure by “Time to exit requirement”, it does not sum.
Any ideas on how to get the measure to sum properly on the graph?
Solved! Go to Solution.
Figured it out. I tried to construct the table within the measure, but it still wasn't working. Eventually I looked up various solutions for measures of "running totals" or "cummulative tolals". This did the trick:
Survivors =
Var TOT_STS = COUNTROWS(FILTER(ALLSELECTED('Student Language Levels'), 'Student Language Levels'[Exit Status] <> "1 N/A" && NOT(ISBLANK('Student Language Levels'[Time to Exit Status]))))
Var TOT_MET =
Calculate(COUNT('Student Language Levels'[Student ID]),
NOT('Student Language Levels'[Exit Status] IN {"1 N/A", "5 Not Yet"})
&& NOT(ISBLANK('Student Language Levels'[Time to Exit Status])),
FILTER(ALLSELECTED('Student Language Levels'), 'Student Language Levels'[Time to Exit Status] <= MAX('Student Language Levels'[Time to Exit Status]))
)
Var Percent_Met = Divide(TOT_MET,TOT_STS)
Var Percent_NOT_Met = Divide(TOT_STS-TOT_MET,TOT_STS)
RETURN Percent_NOT_Met
Will see if I can make it more efficient...
I intentionally avoided using SUMMARIZE in the calculated table, as I knew that I ultimately needed a measure. But now I am wondering if I could use SUMMARIZE also for the measure...
Figured it out. I tried to construct the table within the measure, but it still wasn't working. Eventually I looked up various solutions for measures of "running totals" or "cummulative tolals". This did the trick:
Survivors =
Var TOT_STS = COUNTROWS(FILTER(ALLSELECTED('Student Language Levels'), 'Student Language Levels'[Exit Status] <> "1 N/A" && NOT(ISBLANK('Student Language Levels'[Time to Exit Status]))))
Var TOT_MET =
Calculate(COUNT('Student Language Levels'[Student ID]),
NOT('Student Language Levels'[Exit Status] IN {"1 N/A", "5 Not Yet"})
&& NOT(ISBLANK('Student Language Levels'[Time to Exit Status])),
FILTER(ALLSELECTED('Student Language Levels'), 'Student Language Levels'[Time to Exit Status] <= MAX('Student Language Levels'[Time to Exit Status]))
)
Var Percent_Met = Divide(TOT_MET,TOT_STS)
Var Percent_NOT_Met = Divide(TOT_STS-TOT_MET,TOT_STS)
RETURN Percent_NOT_Met
Will see if I can make it more efficient...
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
46 | |
44 | |
28 | |
22 |