Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bokazoit
Post Patron
Post Patron

Need only to use one date dimension in this measure, how to do that (Sample file included)

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

 

Bokazoit_0-1652777318231.png

 

 

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

 

Indtægter verserende sager = 

VAR MinDato = MIN('FilterDato (Verserende sager)'[DatoFilterKey])
VAR FirstDayQuarter = DATE ( YEAR ( MinDato ), ROUNDUP ( DIVIDE ( MONTH ( MinDato ),3 ),0 ) *3 -2, 1)

RETURN

CALCULATE(
    DISTINCTCOUNT(FactArvesager[ArvesagsKey]),
    FactArvesager[Forventet afsluttet Dato] >= FirstDayQuarter &&
    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:

 

Bokazoit_1-1652777317928.png

 

 

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 = 

VAR MinDato = MIN(DimDato[DatoKey])
VAR FirstDayQuarter = DATE ( YEAR ( MinDato ), ROUNDUP ( DIVIDE ( MONTH ( MinDato ),3 ),0 ) *3 -2, 1)

RETURN

CALCULATE(
    DISTINCTCOUNT(FactArvesager[ArvesagsKey]),
    FactArvesager[Forventet afsluttet Dato] >= FirstDayQuarter && 
    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:

 

Bokazoit_2-1652777318108.png

 

The minimum date is 1. april 2022:

 

Bokazoit_3-1652777317931.png

 

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.

 

Below is sample file attached:

 

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 

 🙂

 

 

 

9 REPLIES 9
Payeras_BI
Super User
Super User

Hi @Bokazoit ,
Let's see if now we got what you wanted when introducing the variable "FirstDayQuarter" in your latest version.

Payeras_BI_1-1652876932501.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

What if I wants to show it with other filters like month etc. should I add that too?

Kumail
Post Prodigy
Post Prodigy

Hello @Bokazoit 

 

The reference image and the file are attached.

Kumail_0-1652797775301.png

https://drive.google.com/file/d/1YABbpnVlDchNNU-bCnI6TvizFioz0U3D/view?usp=sharing

 

Hope this helps.

 

Regards

Kumail Raza

LinkedIn: https://www.linkedin.com/in/kumail-raza-76508856/

 

Sorry, nope that did ot change anything. 

@Bokazoit 

 

In the updated file, the new measure (

2Antal verserende sager) is updated to refer DimDato table and the model updated with DimDato connected to FilterDato to get the report working with DimDato[Dato] in filter visual.
 
Regards
Kumail Raza

When I choose may the result looks like this:

 

Bokazoit_0-1653298151921.png

 

But should look like this:

Bokazoit_1-1653298197054.png

 

According to a MS moderator its because the 7 missing in April is because of the filter when using the measure (be aware it is altered a bit):

Antal verserende sager = 

VAR MinDato = MIN(DimDato[DatoKey])
VAR FirstDayQuarter = DATE ( YEAR ( MinDato ), ROUNDUP ( DIVIDE ( MONTH ( MinDato ),3 ),0 ) *3 -2, 1)

RETURN

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

 As my respons were, it was much easier using report manager.

Payeras_BI
Super User
Super User

Hi again @Bokazoit,

Find the pbix file attached where you will find a duplicate of your Forecast tab where I am using DimDato to slice as per your requirement and not FilterDato.

Payeras_BI_0-1652792714042.png

As per my original post I only changed the MinDato variable.

You will need to adapt the rest of your measures, instead of this:

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

use this:

MinDato = CALCULATE(MIN(DimDato[Dato]),ALLSELECTED(DimDato))

I hope it helps. 

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Payeras_BI
Super User
Super User

Hi @Bokazoit ,

This might work:

TRY THIS = 
VAR MinDato = CALCULATE(MIN(DimDato[Dato]),ALLSELECTED(DimDato))
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 this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

The measure is changed, but I do not see how that would alter anything. but Your idea does not give the expected result:

Indtægter verserende sager = 

VAR MinDato = MIN('FilterDato (Verserende sager)'[DatoFilterKey])
VAR FirstDayQuarter = DATE ( YEAR ( MinDato ), ROUNDUP ( DIVIDE ( MONTH ( MinDato ),3 ),0 ) *3 -2, 1)

RETURN

CALCULATE(
    SUM(FactArvesager[#forventetarv]),
    FactArvesager[Forventet afsluttet Dato] >= FirstDayQuarter &&
    FactArvesager[Bostarts Dato] < MinDato &&
    (FactArvesager[Realiseretarv Dato] = BLANK() || FactArvesager[Realiseretarv Dato] >= MinDato),
    USERELATIONSHIP(FactArvesager[Forventet afsluttet Dato],DimDato[DatoKey])
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.