Forum Discussion
DAX Question: Running Totals with Missing Values by Dimension
Okay. So thanks to the many posts in the Community forum, I have a Running Total measure working (mostly) fine.
Here's my example:
RunningGradTotal = CALCULATE(DISTINCTCOUNT(DegreeConferred[StudentID]),FILTER(ALLSELECTED(DegreeConferred),DegreeConferred[TERM_END_DATE]<=MAX(DegreeConferred[TERM_END_DATE])))
So this DAX equation counts the number of students that have graduated per term, and it keeps a running total so it increasingly increments for each term. Works great.
Now I was able to append the VALUES() segment to give me a Running total based on gender like:
RunningGradTotal_gender = CALCULATE(DISTINCTCOUNT(DegreeConferred[StudentID]),FILTER(ALLSELECTED(DegreeConferred),DegreeConferred[TERM_END_DATE]<=MAX(DegreeConferred[TERM_END_DATE])),VALUES(CohortOriginal[Gender]))
That too works fine, mostly. But here's my problem. We have a term were no student graduated based on this additional dimension. And the Running Total drops to zero (since no record exists) in the term where no student graduated. In the very next term, it jumps back up as it should.
My question is, how can I modify the Running Total to still report a correct running total even when no record exists for that particular timeframe?
So an example looks like:
Term F M
12/FA 5 8
13/FA 27 17
14/FA 74 //No one graduated based on the dimension here, ideally I'd have "17" repeated, rather than a blank
15/FA 89 44 //Running total back in place as necessary.
Any thoughts would be great.
Thanks!
BTW: If there's an easy way to have the DAX script not ignore new dimensions and calculate a running total based on a dimension (like Gender) without having to incorporate the Values() statement for each dimension (and increasing / repeating the number of measurse I have based on the number of dimensions), that would be awesome.
Yeah actually my bad on the ALLSELECTED function, the behaviour is quite different from what I thought it was. Cool you found a soltion.
For the other one, can you change "MAX(DegreeConferred[TERM_END_DATE]))" to the equivalent field in your term table? That way it should have a value even when there isn't a matching entry in the other table.
6 Replies
jahidaImpactful Individual
For showing the missing values, I think the solution is a Date table. There are many people on this forum passionate about the use of date tables, and I'm not one of them, so I'll leave that to someone else.
For the last question, play with using a column name instead of the whole table in the ALLSELECTED. Maybe ALLSELECTED(DegreeConferred[Term]) or ALLSELECTED(DegreeConferred[TERM_END_DATE]) depending on what your table looks like. That way, it won't ignore any other dimensions you put on the matrix.
jahida Thanks for the fast reply. I appreciate your assistance.
For the first, I have a term table (much like the date table) and that is showing the information that I need, but it's just like the DAX script doesn't know to carry forward the Running Total value since that term doesn't exist in the DegreeConferred table for that particular dimension (gender). There are graduates for the female gender in that particular term, just not the male gender in that term.
For the second, I'm sure you're on the right track, but neither work with my equation. When I try DegreeConferred[Term] I get the error message: "A single value for column 'TERM_END_DATE' in table 'DegreeConferred' cannot be determined"...
When I try the second TERM_END_DATE in the AllSelected() field, I get a response. But it's no longer a Running Total, just the total number that graduate in that specific term.
- jahida
Yeah actually my bad on the ALLSELECTED function, the behaviour is quite different from what I thought it was. Cool you found a soltion.
For the other one, can you change "MAX(DegreeConferred[TERM_END_DATE]))" to the equivalent field in your term table? That way it should have a value even when there isn't a matching entry in the other table.