Dax in Power Bi Duplicate issues
Hello. I must get this DAX to work to transfer a report from report builder to Power but but I am having a few PCP locations split the measure data for some reason. here is teh dax: EVALUATE VAR FilteredTable = SUMMARIZECOLUMNS( 'Providers'[PhysicianOrganization], 'Providers'[PCPLocation], 'MemMonths'[MemberID], 'Patients'[InsuranceType], 'DateDim'[Year], 'DateDim'[MonthName], FILTER( 'Patients', 'Patients'[Populations] = "GM" ), FILTER( 'Providers', 'Providers'[HFPN_GMCC_Flag] = "Yes" ), FILTER( 'Outpatient', 'Outpatient'[OfficeVisitEncounter] = "Virtual Visit" ), FILTER( 'DateDim', 'DateDim'[Year] > 2022 ) ) VAR MeasureTable = UNION( ADDCOLUMNS( FilteredTable, "Measure Type", "", "Measure Name", "Allowed PMPM", "Numerator", [AllowedCostYTD], "Denominator", [MemberMonthsYTD], "Rate", IF( ISBLANK([AllowedCostYTD]) || ISBLANK([MemberMonthsYTD]), "0.00%", FORMAT([AllowedPMPMYTD], "Currency") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 1 ), ADDCOLUMNS( FilteredTable, "Measure Type", "Acute Care", "Measure Name", "QPC 1: Admissions Rate per 1,000", "Numerator", [AdmitsYTD], "Denominator", [MemberMonths], "Rate", IF( ISBLANK([AdmitsYTD]) || ISBLANK([MemberMonths]), "0.00%", FORMAT(ROUND([Admits1000YTD], 2), "0.00") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 2 ), ADDCOLUMNS( FilteredTable, "Measure Type", "", "Measure Name", "QPC 2: 30-Day All-Cause Readmissions", "Numerator", [ReadmitsYTD], "Denominator", [AdmitsYTD], "Rate", IF( ISBLANK([ReadmitsYTD]) || ISBLANK([AdmitsYTD]), "0.00%", FORMAT([ReadmitsYTD%], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 3 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "4: Emergency Department Visits per 1,000", "Numerator", [ERVisitsYTD], "Denominator", [MemberMonthsYTD], "Rate", IF( ISBLANK([ERVisitsYTD]) || ISBLANK([MemberMonthsYTD]), "0.00%", FORMAT(ROUND([ERVisits1000YTD], 2), "0.00") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 4 ), ADDCOLUMNS( FilteredTable, "Measure Type", "Chronic Care Management", "Measure Name", "5: cannotlist Care: Hemoglobin A1c less than 8", "Numerator", [cannotlistHbA1cLessThan8Numerator], "Denominator", [cannotlistDenominator], "Rate", IF( ISBLANK([cannotlistHbA1cLessThan8Numerator]) || ISBLANK([cannotlistDenominator]), "0.00%", FORMAT([cannotlistHbA1cLessThan8Rate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 5 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "QPC 6: Controlling High Blood Pressure", "Numerator", [BloodPressureNumerator], "Denominator", [BloodPressureDenominator], "Rate", IF( ISBLANK([BloodPressureNumerator]) || ISBLANK([BloodPressureDenominator]), "0.00%", FORMAT([BloodPressureRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 6 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "Use of Imaging Studies for Low Back Pain (LBP) (Inverse Rate)", "Numerator", [LowBackPainImaging18to64Numerator], "Denominator", [LowBackPainImaging18to64Denominator], "Rate", IF( ISBLANK([LowBackPainImaging18to64Numerator]) || ISBLANK([LowBackPainImaging18to64Denominator]), "0.00%", FORMAT([LowBackPainImaging18to64InverseRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 7 ), ADDCOLUMNS( FilteredTable, "Measure Type", "Preventive Medicine", "Measure Name", "QPC 8: Annual Physical", "Numerator", [AnnualWellnessExamNumerator], "Denominator", [AnnualWellnessExamDenominator], "Rate", IF( ISBLANK([AnnualWellnessExamNumerator]) || ISBLANK([AnnualWellnessExamDenominator]), "0.00%", FORMAT([AnnualWellnessExamRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 8 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "QPC 9: Colorectal Cancer Screening", "Numerator", [ColorectalCancerScreeningNumerator], "Denominator", [ColorectalCancerScreeningDenominator], "Rate", IF( ISBLANK([ColorectalCancerScreeningNumerator]) || ISBLANK([ColorectalCancerScreeningDenominator]), "0.00%", FORMAT([ColorectalCancerScreeningRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 9 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "PC 10: Breast Cancer Screening", "Numerator", [BreastCancerScreeningNumerator], "Denominator", [BreastCancerScreeningDenominator], "Rate", IF( ISBLANK([BreastCancerScreeningNumerator]) || ISBLANK([BreastCancerScreeningDenominator]), "0.00%", FORMAT([BreastCancerScreeningRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 10 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "QPC 11: Cervical Cancer Screening", "Numerator", [CervicalCancerNumerator], "Denominator", [CervicalCancerDenominator], "Rate", IF( ISBLANK([CervicalCancerNumerator]) || ISBLANK([CervicalCancerDenominator]), "0.00%", FORMAT([CervicalCancerRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 11 ), ADDCOLUMNS( FilteredTable, "Measure Type", "Behavioral Health Management", "Measure Name", "QPC 12: Depression Screening for Adolescents & Adults", "Numerator", [DepressionScreeningNumerator], "Denominator", [DepressionScreeningDenominator], "Rate", IF( ISBLANK([DepressionScreeningNumerator]) || ISBLANK([DepressionScreeningDenominator]), "0.00%", FORMAT([DepressionScreeningRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 12 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "QPC 13: F/U for Positive Depression Screening", "Numerator", [DepressionPositiveScreenNumerator], "Denominator", [DepressionScreeningDenominator], "Rate", IF( ISBLANK([DepressionPositiveScreenNumerator]) || ISBLANK([DepressionScreeningDenominator]), "0.00%", FORMAT([DepressionPositiveScreenRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 13 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "QPC 14: F/U After Hospitalization for Mental Illness", "Numerator", [FuInNumerator], "Denominator", [FuInDenominator], "Rate", IF( ISBLANK([FuInNumerator]) || ISBLANK([FuInDenominator]), "0.00%", FORMAT([F/U Hospitalization for Mental Illness], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 14 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "QPC 15: Antidepressant Meds Management - Effective Acute Phase Treatment", "Numerator", [AntidepressantMedicationAcutePhaseNumerator], "Denominator", [AntidepressantMedicationAcutePhaseDenominator], "Rate", IF( ISBLANK([AntidepressantMedicationAcutePhaseNumerator]) || ISBLANK([AntidepressantMedicationAcutePhaseDenominator]), "0.00%", FORMAT([AntidepressantMedicationAcutePhaseRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 15 ), ADDCOLUMNS( FilteredTable, "Measure Type", " Other ", "Measure Name", "QPC 17: Dispense as Written (DAW) Rate", "Numerator", [DAWNumerator], "Denominator", [DAWDenominator], "Rate", IF( ISBLANK([DAWNumerator]) || ISBLANK([DAWDenominator]), "0.00%", FORMAT([DAWFillRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 16 ) ) RETURN MeasureTable For example: Not doing it here: But creating duplicates here:
