Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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'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
    )