Forum Discussion
Adding DISTINCT to CALCULATE
Hi. I have a formula that works out duration of a [FA_TYPE_CD].
However, I need to somehow get the formula to only consider the below for a DISTINCT [FA_ID]. I've tried adding this in to the formula in numerous ways but it keeps failing.
Hi Anonymous ,
Sorry for replying late. Based on your description, you want to calculate the sum job duration for each ID.
You can create a measure like this:
Sum_duration = CALCULATE( SUM('Table'[JOB DURATION]), FILTER( ALL('Table'), 'Table'[FA_ID] in DISTINCT('Table'[FA_ID]) ) )Attached a sample file in the below, hopes to help you.
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.
7 Replies
- Erokor
Resolver II
There are a lot of potential issues:
- you have a measure pointing to a variable, but no return statement.
- Meaning, you need to say RETURN then VAR __FIRSTMEASURE
- I'd add the +0 inside of the CALCULATE function, remember CALCULATE evaluates the Filter argument first, then it executes the expression.
- I'm not sure where the DISTINCT comes in. are you only wanting to sum the distinct durations? or only sum on distinct values?
Avg Minutes on job =
VAR __FIRSTMEASURE =CALCULATE(SUM('SQL'[Job Duration]),'SQL'[FA_TYPE_CD] IN { "BS_BKCO","BS_BKCT" }) + 0 - you have a measure pointing to a variable, but no return statement.
Hi Thanks for replying. I've pasted below the Measure I currently use (apologies, I should have pasted it all before).
Basically I have a dataset that looks something like:
FA_ID
FA_TYPE
JOB_BEFORE
JOB DURATION
PART NUMBER
123
BS_BKCO
25
777
123
BS_BKCO
25
767
124
BS_BKCO
34
576
124
BS_BKCO
34
456
124
BS_BKCO
34
987
What my current measure does is calculates job duration times (disregard VAR_SecondMeasure for just now). FA_ID is the job number and is unique to the job, however there can be multiple rows with the same FA_ID if for example multiple parts were fitted on the job. So in the case of FA_ID 124 there were 3 parts fitted. Although my calculation is flawed because the measure adds each job duration for all FA_IDs, so if I wanted to look at job duration for FA_ID 124 it would return 102 minutes, when I need it to return 34 mins.
So ideally, I need the measure to only calculate at Distinct FA_ID level. I hope that makes sense?
- Erokor
This is the third time I've replied, and the sign-in to this website gets rid of my reply after sign in...
Give this a shot.
MeasureName =
VAR TableToCalc = ALL(Table[FA_ID], Table[FA_Type],Table[Duration])
RETURN
MAXX(TableToCalc, Table[Duration])
If that throws a fit, try it in calculate. CALCULATE(MAX(Table[Duration]),TableToCalc)
v-yingjlCommunity Support
Hi Anonymous ,
Sorry for replying late. Based on your description, you want to calculate the sum job duration for each ID.
You can create a measure like this:
Sum_duration = CALCULATE( SUM('Table'[JOB DURATION]), FILTER( ALL('Table'), 'Table'[FA_ID] in DISTINCT('Table'[FA_ID]) ) )Attached a sample file in the below, hopes to help you.
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.
Avg Minutes Breakdown = VAR __FIRSTMEASURE = CALCULATE( SUM('SQL'[Job Duration]), 'SQL'[FA_TYPE_CD] IN { "BS_BKCO","BS_BKCT" }) + 0 VAR __SECONDMEASURE = CALCULATE( SUM('SQL'[Job Duration]), 'SQL'[FA_TYPE_CD] IN {"AS_RPPT"}, 'SQL'[JOB_BEFORE] IN {"BS_BKCO","BS_BKCT"}) + 0 VAR __COUNT1 = CALCULATE( DISTINCTCOUNT(SQL[FA_ID]), 'SQL'[FA_TYPE_CD] IN { "BS_BKCO","BS_BKCT" }) + 0 VAR __COUNT2 = CALCULATE( DISTINCTCOUNT('SQL'[Job Duration]), 'SQL'[FA_TYPE_CD] IN {"AS_RPPT"}, 'SQL'[JOB_BEFORE] IN {"BS_BKCO","BS_BKCT"}) + 0 RETURN CALCULATE(DIVIDE(__FIRSTMEASURE + __SECONDMEASURE, __COUNT1 + __COUNT2) + 0)
