Forum Discussion
DAX Question: Running Totals with Missing Values by Dimension
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.
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.
Impactful IndividualYeah 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.
jahida I believed that worked. Per your suggestion, I changed my equation to:
RunningGradTotal_gender = CALCULATE(DISTINCTCOUNT(DegreeConferred[StudentID]),FILTER(ALLSELECTED(DegreeConferred),DegreeConferred[TERM_END_DATE]<=MAX(TermInfo[TERM_END_DATE])),VALUES(CohortOriginal))
So my Running Total field goes all the way through every term even ones where there's not a graduate (and where it kind of reaches a "steady" or flat state for no additional, new graduates , but I can always "trim" the terms on the end myself.
Thanks for your help. I think I'm all squared away, for now.
This thread help me to fix my problem. I also had similar issue.
Thanks Guys :)