Forum Discussion
DAX: Cumulative distinctcount incorrect on blank rows
Solution Sage
Just as I thought. You can ditch this model right away. It's simply wrong. Totally wrong. In this model it's not possible to do what you want (or at least it would require such complex code that it would be totally unmaintainable and the code couldn't be understood by mere mortals; compare this to the code I gave you assuming the model is correct). The reasons are very technical but it all boils down to one thing: the dreaded auto-exists problem. This is too technical so I'm not going to enlarge upon this. The solution to this is to create a good model that follows Best Practices set out for PBI (https://docs.microsoft.com/en-us/power-bi/guidance/star-schema). You just have to create a good star-schema, in a word, and pay good attention to how you structure it.
I'll tell you quickly why it's not possible to do what you want. In your fact table not all combinations of attributes are present. So, you put an attribute on your rows---Duration that comes straight from your fact table and by doing this you shoot yourself in the foot immediately because never ever should you place columns from a fact table on the canvas apart from the one case when you troubleshoot issues--- and then you try to intersect this with what you put on columns (Schoolyear). Now, the blanks in your matrix are for combinations of Duration and Schoolyear in the fact table that do not exist. And because you sourced Duration from the fact table, a cell with a blank in it CANNOT see what the current Duration is. Because there is no duration for this combination in the fact table. And because there is no combination like this, the DAX code cannot even know what Duration the row in the matrix is looking at, hence you can't write a measure that would give you a correct cummulative. This is the short story. If you had proper dimensions, you'd write what I gave you (easy formula) and be done with this.
Remember, you should never slice and dice by columns in your fact table. Always via dimensions. This will save you a lot of grief and pulling your hair out.