Need help with measure

[Bokazoit]

This measure is created using a detached Date dimension - as can be seen from this data model:

Doing that makes it possible to calculate the correct distinct count, using this DAX:

Antal verserende sager = 

VAR MinDato = MIN('FilterDato (Verserende sager)'[DatoFilterKey])

RETURN

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

The VAR MinDato gives the  minimum date in my date filter:

But the datamodel is not that userfriendly, and I would like to use the date from my Date dimension (DimDato) instead of the detached date dimension. 

The problem is that when I use the Date dimension it gives me the wrong result. If I use this measure:

Antal verserende sager test = 

VAR MinDato = MIN(DimDato[DatoKey])

RETURN

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

Were I use the Date Dimension (DimDato) instead of the detached date dimension, I get 4 records more:

The minimum date is 1. april 2022:

The green row is correct since it is before the minimum date and the filter in the measure works, but the 4 red rows is not correct as can be seen - since all 4 records is after 1. april 2022.

To me it looks as if the variable MinDato (= minimum date from the datefilter) is calcualted differently than I would expect.

So therefor I need to have a detached date dimension. Can You tell me how to get the date correct?

Any help is really appreciated 🙂

[v-rongtiep-msft]

Hi @Bokazoit ,

How about modifying this measure?

Antal verserende sager test = 

VAR MinDato = MINX(ALL(DimDato),DimDato[DatoKey])

RETURN

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

If it does not help, how about creating relationships like the Filterdato?

If I have misundstood your meaning, please provide your desired output and your  pbix file without privacy information.

How to Get Your Question Answered Quickly 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Bokazoit]

Does not help at al 😞

[v-rongtiep-msft]

Hi @Bokazoit ,

If I have misundstood your meaning, please provide your desired output and your  pbix file without privacy information.

How to Get Your Question Answered Quickly 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Bokazoit]

@v-rongtiep-msft See the pbix file I have added now. Hope for a solution. If I hardcode the date in the measure it works also, but that is not a possible way - ofcourse

[Bokazoit]

Here is a sample file:

https://drive.google.com/file/d/1yjp20i4tZP0GAnSbSe-06f4TtkQowIrh/view?usp=sharing 

There is two sheets, and the results in each sheet is correct, but for the result in the sheet 'Forecast' to be correct, I need to create a new date dimension as described above. I would like to use just the Primary date dimension.

Hope this gives You what You need.

Thx in advance 🙂

[Payeras_BI]

@Bokazoit 

As discussed here. 

[Bokazoit]

I think You should post this in Your own thread 🙂

[lbendlin]

It's not clear what the actual issue is.  Why would a disconnected data table that feeds your slicer be "not user friendly" ?

[Bokazoit]

Because the user have to use one dimension for filtering, and another for date types