Discrepancy in Result with Pick Function in Chart

[Anonymous]

Hi Experts,

I am experiencing a strange issue. I am not sure whether it's because of aggregation function or something else.

I am getting different result when using the same expression with pick function and without pick function. The result without pick is correct. I am not able to figure out why the value changing.

Dim
1
2

Exp1:

Pick(Dim,  Count(Distinct if(aggr(sum([Inv Variance]), [Tran No]) <> 0, [Tran No])),2)

This gives result 184  (incorrect)

Exp2:

Count(Distinct if(aggr(sum([Inv Variance]), [Tran No]) <> 0, [Tran No]))

This gives result 152 (correct)

Please see the document attached.

Let me know if you have any solutions.

Thanks

[sunny_talwar · Accepted solution]

The problem is related to Aggr() function... Within Island/Synthetic dimension, Aggr() doesn't behave the way we would expect... what do you get with this?

Pick(Dim,  Count(Distinct if(aggr(NODISTINCT sum([Inv Variance]), [Tran No]) <> 0, [Tran No])),2)

and see if it changes anyting

[Anonymous]

Thanks Sunny, It's working perfectly. But I am still researching on rationale, if you can shed some light. I want to understand what difference nodistinct is making there.

[sunny_talwar]

Here is one link where troyansky‌ explains Aggr() and Synthetic dimensions:

Q-Tip # 14 – AGGR() and Synthetic Dimensions | Natural Synergies