Could not apply filter outside in column cumulative total.

[phuongui]

Hi,

I have a problem with cumulative total. When I create new calculate column to solve cumulative total (or distinct count) and make a chart timeline with that cumulative total. But when I filter another feature, the value of cumulative didn't change according with filer.

Please help me, thanks.

Column = CALCULATE(
	DISTINCTCOUNT(Demo[name]), ALLSELECTED(Demo),
	Demo[ts] <= EARLIER(Demo[ts]) && Demo[ts] >= EARLIER(Demo[2daybefore]))

In this piece of code, I want to count distinct "name" in Demo table within 2 days window. 

Cumulative Distinct Count (Column)Filter by name = "trang" but value of "Column" did not change

In my expect, when I filter name = "trang" then the table has one row so the value of Column (cumulative distinct count) is 1 not 2.

[v-ljerr-msft · Accepted solution]

Hi @phuongui,

Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report. So it is not possible to create a calculate column/table can change dynamically with user selections on the report.

Could you try using the formula below to create a new measure to see if it works in your scenario? 

Measure =
VAR var_currentTS =
    MAX ( Demo[ts] )
VAR var_2daybefore =
    MAX ( Demo[2daybefore] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Demo[name] ),
        FILTER (
            ALLSELECTED ( Demo ),
            Demo[ts] <= var_currentTS
                && Demo[ts] >= var_2daybefore
        )
    )

Regards

[phuongui]

Thank you, it work 😄