Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

 

 

 

1 ACCEPTED SOLUTION
jahida
Impactful Individual
Impactful Individual

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_D​ATE]))" 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.

View solution in original post

6 REPLIES 6
jahida
Impactful Individual
Impactful 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.

Anonymous
Not applicable

@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
Impactful Individual
Impactful Individual

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_D​ATE]))" 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.

Anonymous
Not applicable

@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 🙂

Anonymous
Not applicable

It looks like for the second question, I can just elimate the field from the VALUES() section and just leave in the table name like:

 

RunningGradTotal_gender = CALCULATE(DISTINCTCOUNT(DegreeConferred[StudentID]),FILTER(ALLSELECTED(DegreeConferred),DegreeConferred[TERM_END_DATE]<=MAX(DegreeConferred[TERM_END_DATE])),VALUES(CohortOriginal))

 

So it looks like I can now add in whatever dimension from the CohortOriginal table, and the DAX will not "play nicely" with it. I can also roll it up to not include any dimensions from the CohortOriginal table in the pivot and it works too. 

 

So @jahida thanks again for your help.

 

Now if I can only get the first question figured out... 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.