Hi All,
I have monthly membership data, each member is in a Category that groups up to a Headline then to an Area. In the fact table I have current month Category and prior month Category.
I want to calculate the number of members who move between Headlines each month. Currently I do this by adding calculated columns to the fact table with 'Headline Prior', 'Headline Current', 'Headline Change'.
The measure for count of members who moved IN to a Headline is:
Count Moved IN to Headline Category =
CALCULATE (
[Member Count],
MEMBERSHIP[Headline Category Change Status] = "Change"
)And moved OUT:Count Moved OUT of Headline Category =
- CALCULATE (
[Member Count],
USERELATIONSHIP ( Dim_MemberCategory[Fee Category], MEMBERSHIP[Fee Category - Prior] ),
MEMBERSHIP[Headline Category Change Status] = "Change"
)My question is, is there a better way to do this? I feel like using columns in the fact table is a lazy way out of a correct solution. Also, this method hardcodes the level (Headline or Area), and I would like the measure to give me the count of members who moved IN/OUT of the current level shown in a visual, be it Headline or Area.
Apologies if this is too rambly or complicated, but any guidance would be appreciated.
Thanks
Rich
