Forum Discussion

Bokazoit's avatar
Bokazoit
Icon for Continued Contributor rankContinued Contributor
4 years ago

Need help with measure

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 🙂

9 Replies

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

    • Bokazoit's avatar
      Bokazoit
      Icon for Continued Contributor rankContinued Contributor

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

  • Bokazoit's avatar
    Bokazoit
    Icon for Continued Contributor rankContinued Contributor

    I think You should post this in Your own thread 🙂

  • Anonymous's avatar
    Anonymous
    Not applicable

    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

      • Anonymous's avatar
        Anonymous
        Not applicable

        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