Filter a Measure for more than 180 days

[okusai3000]

Hello everyone, 

I'm struggling to filter just those Patients with treatments older than 180 days. I made how to get that ammount of days (it took long, but I ended up succesfull!), but now I have problems with the filter:

Adh SAHS 4hs MS2 =

VAR Days =

calculate(DATEDIFF (calculate(selectedvalue('fact Prescripciones'[Fecha Inicio Tratamiento]),CROSSFILTER('fact Prescripciones'[Id Prescripcion],'fact Visitas'[Id Prescripcion],NONE)),

max('dim Fecha'[Fecha]),

DAY),

CROSSFILTER('fact Prescripciones'[Id Prescripcion],'fact Visitas'[Id Prescripcion],NONE))

RETURN

CALCULATE('fact prescripciones'[Pacientes Activos],'fact Monitorizacion'[*Es ultima]=1,'fact Monitorizacion'[*Adh SAHS 4hs]="cumple",filter(all('fact Prescripciones'),Days>180))

Any idea whay could be the problem?

It's worth to say that if I put "Days" as a filter, works flawlesly. The problem is trying to get inside the measure.

thanks!!

[Icey · Accepted solution]

Hi @okusai3000 ,

When using "Days" as a parameter in your expression, it returns just one total value instead of calculating the value of the current row based on the current context. You can check this value by putting "Days" measure into a card and what it shows is the total value. 

So, it is suggested to create "Days" as a measure and then use it as a filter. Or, you can try to create "Days" as a column in virtual table like below.

Adh SAHS 4hs MS2 =
VAR Days =
    CALCULATE (
        DATEDIFF (
            CALCULATE (
                SELECTEDVALUE ( 'fact Prescripciones'[Fecha Inicio Tratamiento] ),
                CROSSFILTER ( 'fact Prescripciones'[Id Prescripcion], 'fact Visitas'[Id Prescripcion], NONE )
            ),
            MAX ( 'dim Fecha'[Fecha] ),
            DAY
        ),
        CROSSFILTER ( 'fact Prescripciones'[Id Prescripcion], 'fact Visitas'[Id Prescripcion], NONE )
    )
VAR t =
    ADDCOLUMNS ( 'fact Prescripciones', "Days_", Days )
RETURN
    CALCULATE (
        'fact prescripciones'[Pacientes Activos],
        'fact Monitorizacion'[*Es ultima] = 1,
        'fact Monitorizacion'[*Adh SAHS 4hs] = "cumple",
        FILTER ( t, [Days_] > 180 )
    )

If this expression doesn't work, please share me some sample data, not real data, for test.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[okusai3000]

That was completely right! Thank you so much Icey!