Forum Discussion

Karolina411's avatar
Karolina411
Icon for Helper V rankHelper V
1 year ago

I have both .rdl files in the google link.  Somehow the UNION of the 16 tables is messing up the calculations.  Perhaps the date?

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Karolina411 

 

Since I can't connect to your data source, I can't see how your paginated report is rendered.

 

But I checked your PBIX, and here are my findings:

 

When the filter is set like the first screenshot in your original post, the reason there are no duplicates seems to be because there is only one piece of data in the table after filtering:

 

When the filter is set like your second screenshot, because the same measure name in the original table has different data:

 

After performing the sum operation on [Numerator] and [Denominator], the problem lies in the [Rate] column.

 

The data formats of the [Rate] column are different. Some are in percentage format, some are in text format, and some are in currency format, so they will be unified into text format. So at present, I think the problem lies in the need to consider unifying the data format of the [Rate] column so that it can be combined in the Visualization.

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Karolina411's avatar
    Karolina411
    Icon for Helper V rankHelper V
    1 year ago

    thanks for your explanation.  I feel like I a close but going back to basics.  All I must do is transition this DAX code in Report Builder to Power Bi.  I am using a tabular model.  Is there a way to do this?

    EVALUATE 
VAR FilteredTable = 
    SUMMARIZECOLUMNS(
        'Providers'[PhysicianOrganization],
        'Providers'[PCPLocation], 
        'Patients'[InsuranceType], 
        'DateDim'[Year], 
        'DateDim'[MonthNameAbbreviation],
        FILTER(
            VALUES('DateDim'[Year]), 
            'DateDim'[Year] = VALUE(@Year)
        ),
        FILTER(
            VALUES('DateDim'[MonthNameAbbreviation]), 
            'DateDim'[MonthNameAbbreviation] = @Month
        ),
        FILTER(
            VALUES('Patients'[Populations]), 
            'Patients'[Populations] = "GM"
        ),
        
        FILTER(
            VALUES('Providers'[PhysicianOrganization]), 
            'Providers'[PhysicianOrganization] = @PhysicianOrganization
        ),
        FILTER(
            VALUES('Providers'[HFPN_GMCC_Flag]), 
            'Providers'[HFPN_GMCC_Flag] = "Yes"
        ),
        FILTER(
            VALUES('Outpatient'[OfficeVisitEncounter]), 
            'Outpatient'[OfficeVisitEncounter] = "Virtual Visit"
        )
    )

RETURN 
    ADDCOLUMNS(
        FilteredTable,
        "UniquePatients", [UniquePatients], 
        "TotalCostYTD", [TotalCostYTD], 
        "MemberMonthsYTD", [MemberMonthsYTD], 
        "Admits1000YTD", [Admits1000YTD],
        "AnnualWellnessExamNumerator", [AnnualWellnessExamNumerator], 
        "AnnualWellnessExamDenominator", [AnnualWellnessExamDenominator], 
        "AnnualWellnessExamRate", [AnnualWellnessExamRate],
        "AntidepressantMedicationAcutePhaseNumerator", [AntidepressantMedicationAcutePhaseNumerator], 
        "AntidepressantMedicationAcutePhaseDenominator", [AntidepressantMedicationAcutePhaseDenominator], 
        "AntidepressantMedicationAcutePhaseRate", [AntidepressantMedicationAcutePhaseRate],
        "ERVisitsYTD", [ERVisitsYTD], 
        "ERVisits1000YTD", [ERVisits1000YTD], 
        "BloodPressureNumerator", [BloodPressureNumerator], 
        "BloodPressureDenominator", [BloodPressureDenominator], 
        "BloodPressureRate", [BloodPressureRate], 
        "DAWNumerator", [DAWNumerator], 
        "DAWDenominator", [DAWDenominator], 
        "DAWFillRate", [DAWFillRate],
        "DiabetesHbA1cLessThan8Numerator", [DiabetesHbA1cLessThan8Numerator], 
        "DiabetesDenominator", [DiabetesDenominator], 
        "DiabetesHbA1cLessThan8Rate", [DiabetesHbA1cLessThan8Rate],
        "FuInNumerator", [FuInNumerator],  
        "FuInDenominator", [FuInDenominator],
        "F/U Hospitalization for Mental Illness", [F/U Hospitalization for Mental Illness],
        "ReadmitsYTD", [ReadmitsYTD],
        "ReadmitsYTDRate", [ReadmitsYTD%],
        "AdmitsYTD", [AdmitsYTD],
        "AllowedCost", [AllowedCost],
        "AllowedCostYTD", [AllowedCostYTD],
        "AllowedPMPMYTD", [AllowedPMPMYTD],
        "BreastCancerScreeningNumerator", [BreastCancerScreeningNumerator],
        "BreastCancerScreeningDenominator", [BreastCancerScreeningDenominator],
        "BreastCancerScreeningRate", [BreastCancerScreeningRate],
        "ColorectalCancerScreeningDenominator", [ColorectalCancerScreeningDenominator],
        "ColorectalCancerScreeningNumerator", [ColorectalCancerScreeningNumerator],
        "ColorectalCancerScreeningRate", [ColorectalCancerScreeningRate],
        "ERVisitsRate", [ERVisitsYTDMoreThan5Visits1000],
        "ERVisitsNumerator", [ERVisitsYTDMoreThan5Visits],
        "VirtualNumerator", [OutpatientUtilYTD],
        "VirtualRate", [OutpatientUtil1000YTD],
        "DepressionScreeningNumerator", [DepressionScreeningNumerator],
        "DepressionScreeningDenominator", [DepressionScreeningDenominator],
        "DepressionScreeningRate", [DepressionScreeningRate],
        "DepressionPositiveScreenNumerator", [DepressionPositiveScreenNumerator],
        "DepressionPositiveScreenDenominator", [DepressionPositiveScreenDenominator],
        "DepressionPositiveScreenRate", [DepressionPositiveScreenRate],
        "CervicalCancerNumerator", [CervicalCancerNumerator],
        "CervicalCancerDenominator", [CervicalCancerDenominator],
        "CervicalCancerRate", [CervicalCancerRate],
        "GenericNumeratorYTD", [GenericNumeratorYTD],
        "GenericDenominatorYTD", [GenericDenominatorYTD],
        "GenericFillRateYTD", [GenericFillRateYTD],
        "LowBackPainImaging18to64Numerator", [LowBackPainImaging18to64Numerator],
        "LowBackPainImaging18to64Denominator", [LowBackPainImaging18to64Denominator],
        "LowBackPainImaging18to64InverseRate", [LowBackPainImaging18to64InverseRate],
        "September 2023 YTD", "September 2023 YTD",
        "Zero", 0
    )