Forum Discussion

Lourini90's avatar
Lourini90
Frequent Visitor
4 years ago
Solved

Aggregate Measure correctly by condition on DATEDIFF

I have the following Table:

 

 

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:

 

 

Any idea how to fix that?

Thank you!

Aggregation
DATEDIFF
DAX
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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.

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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] )

     

    • Lourini90's avatar
      Lourini90
      Frequent Visitor
      4 years ago

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

      any idea? 🙂

       

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

        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.

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    4 years ago

    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 ) )
)

     

    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.