Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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.
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.
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:
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.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |