It's a bit complex so we might need to meet for you to understand what I'm trying to do. I want to think about creating the Member Months in the cube by changing the query where we simply load in the 2M records from InsuranceAlignmentFactX table and within the model (via DAX) create the Member Months calculation which comes out to the 46M records. This means the size of the cube will be much smaller (2M records vs 46M) and the load time will be much quicker.
The problem I haven't been able to solve is including the PCPPanelAlignmentFactX table in this view. If a member has a start date of 1/1/2022 and an end date of 12/31/2022 in InsuraceAlignmentFactX (which is 1 record) then we want to only load in that 1 record and use DAX to have it calculate 12 Member Months. But if the patient flips their PCP say each month (in PCPPanelAlignmentFactX) how can/should we store the data so that we keep the minimum number of records and are able to show this view for each PCP? I'm stuck and need some brainpower.
Here is the result of a basic insurance query and I am using an example based off of an Insurance Key:
InsuranceAlignmentKey = '2057981'
InsuranceAlignmentKey PatientDurableKey AlignmentStartDate AlignmentEndDate Count
2057981 8538933 2016-07-01 2023-06-01 1
Here is the actual query
SELECT I.InsuranceAlignmentKey
, I.PatientDurableKey
, I.AlignmentStartDate
, ISNULL(I.AlignmentEndDate, I2.LastDate) AS [AlignmentEndDate]
, I.Count
FROM InsuranceAlignmentFactX AS I
LEFT JOIN (
SELECT I1.Insurance
, I1.InsuranceType
, MAX(I1.AlignmentStartDate) AS [LastDate]
FROM InsuranceAlignmentFactX AS I1
GROUP BY I1.Insurance
, I1.InsuranceType
) AS I2 ON I.Insurance = I2.Insurance
AND I.InsuranceType = I2.InsuranceType
WHERE I.InsuranceAlignmentKey = '2057981'
ORDER BY I.AlignmentStartDate
I use this DAX To sum up each patient by month
1st I count the # of Members =
SUM([Count])
2nd I create a summary table count all members within each month of the year
MemberMonthsTemp =
VAR EndDate = MAX(DimDate[DateValue])
VAR EndDateShift = EOMONTH(EndDate,-1)
VAR MemberStart = CALCULATE(SUM([Count]),ALL(DimDate),FactMemberMonths[AlignmentStartDate]<=EndDate)
VAR MemberEnd = CALCULATE(SUM([Count]),ALL(DimDate),FactMemberMonths[AlignmentEndDate]<=EndDateShift)
RETURN IF(HASONEFILTER(DimDate[YearMonth]),MemberStart-MemberEnd,SUMX(VALUES(DimDate[YearMonth]),MemberStart-MemberEnd))
3rd – I sum up the values by month ,etc
MemberMonths = IF(SUMX(VALUES(DimDate[YearMonth]),[MemberMonthsTemp])=0,BLANK(),SUMX(VALUES(DimDate[YearMonth]),[MemberMonthsTemp]))
Here is a result when I join the related PCP and Patients can change them
Every month so it creates a large data load but I need the PCP to show
InsuranceAlignmentKey AlignmentStartDate AlignmentEndDate PCPEffDate PCPEndEffDate PCPProviderDurableKey
2057981 2016-07-01 NULL 2016-10-01 2017-03-31 9822476
2057981 2016-07-01 NULL 2017-04-01 2018-03-31 9872105
2057981 2016-07-01 NULL 2018-04-01 2018-05-31 9822476
2057981 2016-07-01 NULL 2018-06-01 2019-07-31 9616447
2057981 2016-07-01 NULL 2019-08-01 2020-03-31 9822476
2057981 2016-07-01 NULL 2020-04-01 2020-05-31 9616447
2057981 2016-07-01 NULL 2020-06-01 2021-07-31 9822476
2057981 2016-07-01 NULL 2021-08-01 2022-01-31 9917955
2057981 2016-07-01 NULL 2022-02-01 2022-07-31 9822476
2057981 2016-07-01 NULL 2022-08-01 NULL 9814336
And Here is that actual query
SELECT I.InsuranceAlignmentKey
, I.AlignmentStartDate
, I.AlignmentEndDate
, P.PCPEffDate
, P.PCPEndEffDate
, P.PCPProviderDurableKey
FROM InsuranceAlignmentFactX AS I
LEFT JOIN PCPPanelAlignmentFactX AS P ON I.InsuranceAlignmentKey = P.InsuranceAlignmentKey
WHERE I.InsuranceAlignmentKey = '2057981'
ORDER BY I.AlignmentStartDate
, P.PCPEffDate
