Hello,

I've got fact table with visits to objects. Each visit has id of used Pos (point of sale). Poss are located in locations. Pos and location are placed in dimension DimLocation and are in hierarchy pos (leaf) - location (parent).

I have to have measure (calculated member): number of visited locations.

For example for data:

FactVisit
Id Cost PosId
1 2 1
2 5 2
3 2 1
4 1 3
5 1 2
6 3 4
7 2 5

DimLocation
PosId LocationId
1 1
2 1
3 2
4 1
5 3
6 4
7 7

measure should be 3 (locations 1, 2 and 3 visited). How to write this calculated member?

Second problem is similar but applies to many-to-many dimension. I've got also virtual locations that are in many-to-many relation with poss. DimVirtualLocation is many-to-many dimension to FactVisit (through VirtualLocation2Pos fact (bridge) table). I need similar measure here: Number of visited virtual locations.

For example for data similar as above and following VirtualLocation2Pos table:

VirtualLocatio2Pos
PosId VLId
1 1
1 2
1 3
2 4
2 1
3 2
3 5
5 3

measure should be 5 (virtaul locations 1, 2, 3, 4 and 5 visited). How to write this measure?

Appreciate your help.