Count common elements within filtered values

#1

Hi All,

I am quite new to Dundas, but after doing quite reading in the community and documentation, I still cannot find a way to achieve the following:

I am looking for a way to count common values withing filtered values.

For the example, I have table that have Type and Key fields:

Type KEY
A 1
A 2
A 3
B 1
B 4
B 5
C 1
C 4
D 1
D 4
D 5

I would like to have a result, which will give me the count of keys, that are meat in each Type that is selected in filter. So, if the filter is:

  • A,B,C,D -> Result should be 1 (because only Key = 1 is found on all Types);
  • A,B -> Result should be 1 (because only Key = 1 is found in Type A and B);
  • B,C -> Result should be 2 (because Keys = 1 and 4 are found it Types B and C);
  • B,D -> Result should be 3 (because Keys = 1, 4 and 5 are found it Types B and D);

Any ideas?

#2

I can think of a potential 2 part solution. For part 1, you will want to add a count measure (1’s all the way down, can be done from SQL select transform) and pivot your data, which can be done at cube level, so that A, B, C, and D are all separate columns isntead of Types within a dimension column.

https://www.dundas.com/Support/learning/documentation/data-metrics/transforms/other/pivot

You can pivot it so that the count measure is used as basis for the value and the dimension to turn into pivot columns is Type. Then at that point, for part 2, you can create a calculated element, which generates a comma-delimited string by going through the 4 type columns and checking to see if there is a 1 in there for a particular key.

https://www.dundas.com/support/learning/documentation/cleanse-consolidate-modify-data/transforms/common/calculated-element