Hi all,
I have an issue with a Sum of diferent values for a dimension that is a lower level respect fact table.
My fact table don't include a dimension that I need for work with:
| FACT TABLE |
| Id_Person | Age | Gender | Affected |
| 1 | 3 | Male | 1 |
| 2 | 1 | Female | 1 |
| 3 | 14 | Male | 1 |
| 4 | 33 | Male | 1 |
| 5 | 20 | Female | 1 |
| 6 | 45 | Male | 1 |
| 7 | 67 | Male | 1 |
| 8 | 74 | Female | 1 |
Dimension:
| DIMENSION TABLE |
| Dimension | Sum (Affected) | Condition |
| Dim1 | 3 | <15 |
| Dim2 | 2 | >15 & Female |
| Dim3 | 2 | >65 |
| Dim4 | 8 | ALL |
| Dim5 | 8 | ALL |
| … | … | … |
| Total | 8 | |
Although 'Dimension' is not included in Fact Table, it is possible work with it joining this dimension by different conditions like Age and Gender. I need obtain something like this, but additionally I need work with multiple values for this dimension:
| Expressions |
| Selected Dimension | Sum(Affected) |
| Dim1;Dim2 | 5 |
| Dim2;Dim3 | 3 |
| Dim1;Dim2;Dim3 | 6 |
| Dim1;Dim2;Dim4 | 8 |
| Nothing in Selector | 8 |
I try to get this result:
| Expedted | | |
| Dimension | Cost | Affected people |
| Total | 6000 | 8 |
| Dim1 | 1000 | 3 |
| Dim2 | 1000 | 2 |
| Dim3 | 2000 | 2 |
| Dim4 | 1500 | 8 |
| Dim5 | 500 | 8 |
with a Set Analisys like:
Sum({<$(PARAM Excluded Dimensions) >} $(PARAM Conditions) FT_AFFECTED_PEOPLE))
where:
$(PARAM Excluded Dimensions) are Dimensions including 'Dimension'. I need this clause for obteining the correct value.
$(PARAM Conditions) is a CASE expression that add filtering condition for fact.
FT_AFFECTED_PEOPLE is a simple registrer count from Fact Table.
With this solution I can obtain the Total Count of affected people but don't get the value of each dimension:
| Solution1 | | |
| Dimension | Cost | Affected people |
| Total | 6000 | 8 |
| Dim1 | 1000 | 0 |
| Dim2 | 1000 | 0 |
| Dim3 | 2000 | 0 |
| Dim4 | 1500 | 0 |
| Dim5 | 500 | 0 |
This solution works fine for a text box with a totalizer, but in a Pivot Table with 'Dimension' is not a fine solution.
Other solution that I tried are Aggregator by 'Dimension' and modify script for get a field with a value for each value of 'Dimension', but I can't get a correct solution.
What can I do for get correct solution avoiding cross join between Dimension and Fact Table?
Thank you in advance.
