Data Island Exclusion set analysis

[gmenoutis]

I have a customers table, with [Customer Code] being the linking field, and a transactions table which among others includes [Transaction Store Code] and [Customer Code].

I also have made a data island which includes the field [NOT Transaction Store Code]. The point is to have a table with the customer list such that when some [NOT Transaction Store Code]s are selected, the [Customer Code]s which have some transactions on these stores are excluded.

I have thus created the following calculated dimension:

aggr(only({<[Transaction Store Code]-=[NOT Transaction Store Code]>} [Customer Code]),[Customer Code])

and set it to not show nulls.

This thing indeed does what I want. Unfortunately, it also hides [Cutomer Code]s which have no transactions at all. Is there any way to get around this?

[gmenoutis]

Although it seems to work for most cases, it doesn't work for selecting both S1 and S2: instead of C3 it filters nothing and shows C1,C2,C3.

[sunny_talwar · Accepted solution]

Try this

=Aggr(If(GetSelectedCount([NOT Transaction Store Code]) = 0, [Customer Code], Only({<Flag = {1}>-<[Transaction Store Code] = p([NOT Transaction Store Code])>} [Customer Code])), [Customer Code])

[gmenoutis]

Well, this works! Thank you very much!

Could you please elaborate how it works? Some questions I can produce are the following:

1) Why is the Flag=1 modifier needed? I actually notice that I can change this to $ or 1 with the same results.

2) Why is the p needed, whereas the following doesn't work: [Transaction Store Code] -= [NOT Transaction Store Code]

3) Why is the if getselectedcount needed?

I hope I can get some deeper understanding of this.

[sunny_talwar]

1) Having thought about it a little more, you don't really need Flag... you can use this

=Aggr(If(GetSelectedCount([NOT Transaction Store Code]) = 0, [Customer Code], Only({<[Customer Code] = {"*"}>-<[Transaction Store Code] = p([NOT Transaction Store Code])>} [Customer Code])), [Customer Code])

2) p() is giving possible value and we are using - to remove that from the set of all Customer Codes... see the negative sign in red

=Aggr(If(GetSelectedCount([NOT Transaction Store Code]) = 0, [Customer Code], Only({<[Customer Code] = {"*"}>-<[Transaction Store Code] = p([NOT Transaction Store Code])>} [Customer Code])), [Customer Code])

4) We need the GetSelectedCount() because when nothing is selected, all values are possible within [NOT Transaction Store Code] which is equivalent of selecting both S1 and S2. In order to differentiate between when both are selected vs when nothing is selected... we use an if statement

When no [NOT Transaction Store Code] is selected, just use the simple Customer Code as dimension... else use Only({<[Customer Code] = {"*"}>-<[Transaction Store Code] = p([NOT Transaction Store Code])>} [Customer Code])

[gmenoutis]

Nice, thank you again for your insight!

Actually, for future reference, it seems I can get rid of the if, if I don't use p():

=Aggr(

Only({$-<[Transaction Store Code] = [NOT Transaction Store Code]>} [Customer Code])

, [Customer Code])

This makes it also easier for me to add more exclusions if needed