DAX: Cumulative distinctcount incorrect on blank rows

[BI_diva]

Hi, I am unable to get a correct cumulative distinctcount, because my table has blank rows for some columns.

The formula for NoStudents I used is:

CALCULATE(

DISTINCTCOUNTNOBLANK(MyTable[NoStudents]),

    FILTER(

        ALL(MyTable[Duration])

        , MyTable[Duration] <= MAX(MyTable[Duration])

     )

)

In the example below, what I would expect is that CumStudents y1 for the Duration 4  & 5 years would be 35 and not 5.

Does anyone know how to solve this?

MyTable:

Duration (years)	NoStudents y1	CumStudents y1	NoStudents y2	CumStudents y2	NoStudents y3	CumStudents y3
0	5	5	5	5	10	10
1	10	15	10	15	10	20
2	10	25	10	25	10	30
3	10	35	10	35	10	40
4		5		5	10	50
5		5	10	45	10	60

[daxer-almighty]

@BI_diva 

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.

[Anonymous]

var LastDurationVisible = MAX( MyTable[Duration] )
return
	CALCULATE(
		SUM( MyTable[NoStudents] ),
		MyTable[Duration] <= LastDurationVisible
	)

[BI_diva]

Hi Daxer, thank you for your suggested answer. I have just given it a try, but unfortunately the result in the table is still the same.

[Anonymous]

You have to show more than what you have. Certainly, my formula is totally correct for the table shown and I can  totally prove it. If it does not work for you, it means you've got something in there that is different than what you've shown.

[BI_diva]

Hi Daxer, here is a link to the .pbix file: https://1drv.ms/u/s!As7R5dcyGWRKgjaQk8A-9fWvnO3W?e=ilibVn