Running total with null values in the fact tables

[Anonymous]

Please help,

I have created a "Churned" measure that is supposed to count the Running total of churned customers by days:

Churned = CALCULATE(COUNTROWS(MyData), FILTER( ALL(Days),  Days[Day]<=MAX(Days[Day])),not(ISBLANK(MyData[Days To Churn]))). In my fact table - the clients that haven't churned have null in the "Days To Churn Field", so I have added the last part...

In my report I still see null value in my Days dimension.

Is there a way to eliminate the null by alternating my measure - without filtering the Visual?

Thanks!

The .pbix file

https://1drv.ms/u/s!AoP_9ampPIT7yHYIkCSTho7lE-9Q?e=eQvqi0

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use the following measure to meet your reqirement:

Churned =
IF (
    COUNTROWS ( 'Days' ) = 0,
    BLANK (),
    CALCULATE (
        COUNTROWS ( MyData ),
        FILTER ( ALLSELECTED ( Days ), Days[Day] <= MAX ( Days[Day] ) ),
        NOT ( ISBLANK ( 'MyData'[Days To Churn] ) )
    )
)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

Best regards,

[Anonymous]

Awesome, thanks, @v-lid-msft !