Two weeks ago I create a discussion about a aggregation for a dimension not included in fact table.
Today, I am searching a similar solution for a dataset with more complexity.
I have a Fact Table that concatenating three different data sources.
Using the same example from my previous post:
Fact table contain a concatenation of 3 differents data sources. Only one of them has Specialty (and Cost) information.
|DIMENSION TABLE Specialty|
|Gynecology||>15 & Female|
I need a table with two expression that calculates Cost by Specialty and Affected People by Specialty (It is not a count of fact table, it is a record counter filtered by condition). So:
|Pediatrics||4500||6 (3+2+2-1, this one is for a woman in Gyn & Ger)|
I try to get this result with a Sum(Cost) and pick(match(Affected)) but pick & match doesn't work fine in a table with Specialty.
How can I get these two expression correctly?
Thank you in advance.