Dax in Power Bi Duplicate issues

[Karolina411]

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:

[Anonymous]

Hi @Karolina411 

It seems that you are doing the union of 16 tables, and then there are duplicate measure types, which seems to be caused by different results of [DAWNumerator] or [DAWDenominator]. However, since DAX is too complicated and involves too much data and other measures, I cannot clearly know the problem based on DAX and screenshots. Could you please provide your files or sample data? How to provide sample data in the Power BI Forum - Microsoft Fabric Community Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

In addition, I need to confirm with you that you said "transfer a report from report builder to Power", but from your screenshot, it seems that it is not the interface of report builder. Could you please explain the meaning of this sentence? Do you need to publish the report to Power BI Service?

Thank you for your time and efforts in advance.

Best Regards,
Yulia Xu

[Karolina411]

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]

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]

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
    )

[Karolina411]

https://drive.google.com/drive/folders/1f2dNUj2ZOOsdVaW3B2TXNAFVuO_ALEGF?usp=sharing 

Here there are 2 files-an .rdl (correct numbers) and a .pbix (incorrect numbers).  When you slice by PCPLocation some are split up for some reason. 

My Dax Queries are all different , too.  Very strange.