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
Lourini90
Frequent Visitor

Aggregate Measure correctly by condition on DATEDIFF

I have the following Table:

 

Lourini90_0-1634572686778.png

 

It represents processes with a certain category.

And there is also a Date Table over column TIMESTAMP.

I would like to show a Measure based on another Measure that calculates the Date-Difference until the selected Date.

So first this is how I calculate the Date-Difference:

 

AGE = 
VAR SELECTED_DATE = CALCULATE(MAX(DATUM[Date]), ALLSELECTED(DATUM))
VAR STARTDATE_PROCESS = Calculate(MAX(Workflow[MIN_TIMESTAMP]),DATUM[Date]<=MAX(DATUM[Date]), ALL(DATUM[Date]))
RETURN
DATEDIFF(STARTDATE_PROCESS,SELECTED_DATE,DAY)

 

Now I want to use a Measure which depends on the result of AGE, like

 

NEW = IF([AGE]<=3,CALCULATE(COUNT(Workflow[PROCESS]),ALL(DATUM)))

or

 

OLD = IF([AGE]>3,CALCULATE(COUNT(Workflow[PROCESS]),ALL(DATUM)))

 

That works so far, but the aggregation is not working correctly as you can see here, there should be a 3 on NEW and OLD aggregated:

 

Lourini90_1-1634572720429.png

 

Any idea how to fix that?

Thank you!

1 ACCEPTED SOLUTION

Hmm. I think ALLSELECTED ( DATUM ) is destroying the PROCESS filter context. Try ALLSELECTED ( DATUM[Date] ) instead.

 

I'm not sure that this will work though. There's a decent chance you'll have auto-exists trouble without a date dimension table.

View solution in original post

4 REPLIES 4
v-yingjl
Community Support
Community Support

Hi @Lourini90 ,

You can try to modify the measures like this:

NEW = 
IF (
    HASONEVALUE ( 'Workflow'[PROCESS] ),
    IF ( [AGE] <= 3, CALCULATE ( COUNT ( 'Workflow'[PROCESS] ), ALL ( DATUM ) ) ),
    CALCULATE ( COUNT ( Workflow[PROCESS] ), FILTER ( 'Workflow', [AGE] <= 3 ) )
)
OLD = 
IF (
    HASONEVALUE ( 'Workflow'[PROCESS] ),
    IF ( [AGE] > 3, CALCULATE ( COUNT ( 'Workflow'[PROCESS] ), ALL ( DATUM ) ) ),
    CALCULATE ( COUNT ( Workflow[PROCESS] ), FILTER ( 'Workflow', [AGE] > 3 ) )
)

vyingjl_0-1634796696090.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

AlexisOlson
Super User
Super User

You need to sum over each process individually or else the maximums in AGE are taken over all of the processes simultaneously rather than one at a time.

 

SumNew = SUMX ( VALUES ( Workflow[PROCESS] ), [New] )

 

Thanks for replying!
For some reason nothing is shown here:

Lourini90_0-1634579779593.png

any idea? 🙂

 

Hmm. I think ALLSELECTED ( DATUM ) is destroying the PROCESS filter context. Try ALLSELECTED ( DATUM[Date] ) instead.

 

I'm not sure that this will work though. There's a decent chance you'll have auto-exists trouble without a date dimension table.

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.

Top Solution Authors