Today let’s talk about how to create different aggregation formula for each fact table used in a fact in Microstrategy.
Let’s say that we need to create a SUM(value) for Fact A and a COUNT(value) for Fact B. When we get data from Fact A, Microstrategy must SUM, when in Fact B, Microstrategy must COUNT.
To do that you just have to:
1. Create a new Fact;
2. Create a new expression with:
ApplySimple(“Count #0”, FIELD1)
Only use Fact B for source tables.
3. Create a new expression with:
ApplySimple(“Sum #0”, FIELD2)
Only use Fact A for source tables.
Save your Fact as: Fact_Test
4. Create a new Metric with:
5. Update your Schema.
Hope it helps
God bless you!