Sometimes project requires that you join fact entities, causing the records redundancy. Let’s see an example

“Entity1ID” is a key value for Entity1 and “Entity2ID” is a key value for Entity2. In this case Entity1 and Entity2 ale related as many to many. The keys are not unique in this fact.

When we want to count unique members of Entity1 you may use the distinctcount aggregation on column “Entity1ID”. However if you would like to sum Quantity values of unique members of one entity you have no aggregation for that.

If we used the Sum aggregation on “Entity1Quantity” column we would get the result of 105. This is true because we added Quantity 15 and three occurrences of Quantity 30.

Result of 105 is not what we want because there are only 2 different Entities 1, so the expected value should be 45. Unfortunately there is no DINSTINCTSUM aggregation in SSAS

To resolve the problem, you have to divide every partial sum referring to the Entity1 by the number of different members of Entity2 matched with that entity. If you want your solution to be really useful don’t perform this division in TSQL. Remember, you may sometimes cut the results in MDX expression of which the TSQL in ETL is not aware of.

You will have to type the calculate member in MDX expression.

You will need:

• [Measures].[Entity2DisticntCount] – DISTINCTCOUNT of Entity2ID
• [Measures].[Entity1Quantity] – SUM of Entity1Quantity
• [Entity1].[Entity1ID] – Dimention of Entity1 with Attribute related with Entity1ID

The MDX Expression IS:

SUM
(
[Entity1].[Entity1ID].levels(1).MEMBERS,
[Measures].[Entity1Quantity]
/
([Measures].[Entity2DisticntCount],[Entity1].[Entity1ID].currentmember)
)