How to modified my expression to get distinct count?

[Anonymous]

Hi guys, How should i modified my expression to ensure no duplicate for SR No. the value i want is 83, not the total of 0+A+B+C.

Formula to get Gate C:

sum(

Aggr (

if(

(Count({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*$<[SR No]-={"''"}>

*$<[Gate]={"Gate C"}>

}(Gate))-count([SR No]))>0,

(Count ({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*$<[SR No]-={"''"}>

*$<[Gate]={"Gate C"}>

} distinct(Gate))),

'No repetition') ,  [SR No]))

-----------------------------------------------------------------------------------------------------------------------------------------------

Formula to get Gate 0+A+B+C:

(//B

sum(

Aggr (

if(

(Count({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*$<[SR No]-={"''"}>

*$<[Gate]={"Gate B"}>

}(Gate))-count([SR No]))>0,

(Count ({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*$<[SR No]-={"''"}>

*$<[Gate]={"Gate B"}>

} distinct(Gate))),

'No repetition') ,  [SR No]))

+

//C

sum(

Aggr (

if(

(Count({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*$<[SR No]-={"''"}>

*$<[Gate]={"Gate C"}>

}(Gate))-count([SR No]))>0,

(Count ({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*$<[SR No]-={"''"}>

*$<[Gate]={"Gate C"}>

} distinct(Gate))),

'No repetition') ,  [SR No]))

+

//0

sum(

Aggr (

if(

(Count({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*$<[SR No]-={"''"}>

*$<[Gate]={"Gate 0"}>

}(Gate))-count([SR No]))>0,

(Count ({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*$<[SR No]-={"''"}>

*$<[Gate]={"Gate 0"}>

} distinct(Gate))),

'No repetition') ,  [SR No]))

+

//A

sum(

Aggr (

if(

(Count({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*$<[SR No]-={"''"}>

*$<[Gate]={"Gate A"}>

}(Gate))-count([SR No]))>0,

(Count ({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*$<[SR No]-={"''"}>

*$<[Gate]={"Gate A"}>

} distinct(Gate))),

'No repetition') ,  [SR No]))

)

stalwar1‌ please help. Thanks.

[ogautier62]

Hi,

if you want the count without repetition,

don't make addition, but just one expression with :

*$<[Gate]={"Gate0","GateA","Gate B","GateC"}>

regards

[sunny_talwar]

I have no idea how to decipher your expression by just looking at the script and the image... would you be able to share a sample to check this out?

[Anonymous]

Dear Sunny,

Please check. Thanks.

stalwar1‌

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(

RangeMax(//B

If(Count({$<[SR No] = {"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate B"}>}Gate)-Count([SR No]) > 0,

Count({$<[SR No] = {"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate B"}>} DISTINCT Gate), 'No repetition'),

//C

If(Count({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate C"}>}Gate) - Count([SR No]) > 0,

Count({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate C"}>} DISTINCT Gate), 'No repetition'),

//0

If(Count({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate 0"}>}Gate) - Count([SR No]) > 0,

Count({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate 0"}>} DISTINCT Gate), 'No repetition'),

//A

If(Count({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}, [Gate]={"Gate A"}>} Gate) - Count([SR No]) > 0,

Count({$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate A"}>} DISTINCT Gate), 'No repetition')

)

, [SR No]))

[Anonymous]

Hi Sunny,

Now i know where i get it wrong. Already try to use rangemax before.

Thanks !!!