cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lourini90
Regular 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.

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors