Need this measure to filter out values but it doesn't, what am I missing?

[Bokazoit]

I have this measure:

Antal verserende sager = 

VAR MaksDato = MIN(DimDato[DatoKey])

RETURN

CALCULATE(
    DISTINCTCOUNT(FactArvesager[ArvesagsKey]),
    FILTER(FactArvesager,FactArvesager[Bostarts Dato] < MaksDato),
    FILTER(FactArvesager,FactArvesager[Realiseretarv Dato] = BLANK() || FactArvesager[Realiseretarv Dato] >= MaksDato),
    USERELATIONSHIP( FactArvesager[Forventet afsluttet Dato], DimDato[DatoKey] )
)

All relations to the date dimension is activated by USERELATIONSHIP since the calculations have different scope.

The above measure does not seem to filter out correctly. Look at this record:

[Bostarts dato] is higher than the filter VAR MaksDato = MIN ( DimDato[DatoKey] ) is equal to 1. april 2022

If the measure should work it would not have the record, but it does. The odd part is that the total is correct, but the totals pr. quarter:

The 75 as total is correct, but sum op the numbers gives 78, and the record above is part of the reason.

what am I missing in my filtercontext?

[Bokazoit]

This is what data looks like:

No matter what I do - see datamodel above, with my measure:

Antal verserende sager fix = 

VAR MinDato = MIN(DimDato[Dato])

RETURN

CALCULATE(
    DISTINCTCOUNT(FactArvesager[ArvesagsKey]),
    FactArvesager[Realiseretarv Dato] = BLANK() && FactArvesager[Bostarts Dato] < MinDato
)

it will not filter the last 3 records and since the minimum data is 1.  april 2022, they should be gone.

Any help will be greatly appreciated

[Bokazoit]

If I change the Variable date to a fixed date? Then it works splended:

Antal verserende sager = 

VAR MinDato = DATE(2022,4,1)

RETURN

CALCULATE(
    DISTINCTCOUNT(FactArvesager[ArvesagsKey]),
    FILTER(FactArvesager,FactArvesager[Forventet afsluttet Dato] >= MinDato),
    FILTER(FactArvesager,FactArvesager[Bostarts Dato] < MinDato),
    FILTER(FactArvesager,FactArvesager[Realiseretarv Dato] = BLANK() || FactArvesager[Realiseretarv Dato] >= MinDato)
)

Before:

Antal verserende sager = 

VAR MaksDato = MIN(DimDato[DatoKey])

RETURN

CALCULATE(
    DISTINCTCOUNT(FactArvesager[ArvesagsKey]),
    FILTER(FactArvesager,FactArvesager[Bostarts Dato] < MaksDato),
    FILTER(FactArvesager,FactArvesager[Realiseretarv Dato] = BLANK() || FactArvesager[Realiseretarv Dato] >= MaksDato),
    USERELATIONSHIP( FactArvesager[Forventet afsluttet Dato], DimDato[DatoKey] )
)

Does that explain anything to You?

This is the datamodel:

And the result after fix the date:

I can not use the fixed date. It needs to be dynamic depending on the date set by the date filter using my date dimension.

[johnt75]

The main difference I see is that the measure which is working with the fixed date is not using USERELATIONSHIP, so it will be using the active relationship instead. That may be filtering out the rows in question.

[Bokazoit]

It doesn't make any difference. I think what I need is a way to make sure that the Variable is fixed. Because it does not take this part into account:

FactArvesager[Bostarts Dato] < MaksDato

[Fowmy]

@Bokazoit 

Can you try the following version?

Antal verserende sager =
VAR MaksDato =
    MIN ( DimDato[DatoKey] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( FactArvesager[ArvesagsKey] ),
        FactArvesager[Bostarts Dato] < MaksDato,
        FactArvesager[Realiseretarv Dato] = BLANK ()
            || FactArvesager[Realiseretarv Dato] >= MaksDato,
        USERELATIONSHIP ( FactArvesager[Forventet afsluttet Dato], DimDato[DatoKey] )
    )

[Bokazoit]

I still get those with date in april?

See 62027 og 62028

[johnt75]

I think the multiple calls to FILTER might be interfering with one another. Try

Antal verserende sager = 

VAR MaksDato = MIN(DimDato[DatoKey])

RETURN

CALCULATE(
    DISTINCTCOUNT(FactArvesager[ArvesagsKey]),
    FactArvesager[Bostarts Dato] < MaksDato &&
    (FactArvesager[Realiseretarv Dato] = BLANK() || FactArvesager[Realiseretarv Dato] >= MaksDato),
    USERELATIONSHIP( FactArvesager[Forventet afsluttet Dato], DimDato[DatoKey] )
)

[Bokazoit]

Gives me errors, something is missing

[johnt75]

There was an extra ), I've edited the original post

[Bokazoit]

Thank You but it still counts wrong. I like to use the Date dimension for the measure. But for total it is correct but not for each Year: