Forum Discussion
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
- lbendlin
Super User
It's not clear what the actual issue is. Why would a disconnected data table that feeds your slicer be "not user friendly" ?
- Bokazoit
Continued Contributor
Because the user have to use one dimension for filtering, and another for date types
- Bokazoit
Continued Contributor
I think You should post this in Your own thread 🙂
- AnonymousNot 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
- Bokazoit
Continued Contributor
Does not help at al 😞
- AnonymousNot 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