Forum Discussion

Karolina411's avatar
Karolina411
Icon for Helper V rankHelper V
2 years ago

Filtering with a sub filter with multiple value selection - will not work for Report Builder PR

I am working on a Paginated report in REPORT BUILDER that the user would like to: Select date (Month/Year) then the PhysicianOrganization then the PCPLocation to have a pdf for that location only. ...
@PaginatedReports
DAX Filtering
Help
Karolina411's avatar
Karolina411
Icon for Helper V rankHelper V
2 years ago

Thank you so much for your incredible patients Jianpeng Li .  i did encounter this error: PhysicianOrganization

as it must be declared but when i declare it this DAX still does not work: EVALUATE
SUMMARIZECOLUMNS (
'Providers'[PCPLocation],
RSCustomDaxFilter (
@PhysicianOrganization,
EqualToCondition,
[Providers].[PhysicianOrganization],
String
)
)

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, Karolina411 

Thank you for your reply. I also encountered the error you mentioned during the test. Here is how to solve it:

Here are the parameters and dataset I currently have, I will reproduce your error and suggest a solution in the steps below:

I get your error when I follow the steps below to create a dataset:

Here are the correct steps:

The main reason was that the parameter we created (report level) was not correctly recognized by the query engine even though I bound it correctly. My correct steps above utilized the query parameters generated by the query designer and then manually bound to our existing parameters.

 

 

Best Regards

Jianpeng Li

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
    2 years ago

    Thanks so much but it did not work and it is not giving me an error message.  i will try again: 

     

     

  • Karolina411's avatar
    Karolina411
    Icon for Helper V rankHelper V
    2 years ago

    Thank you.  I started all over again. Where are you pointing the PhysicianOrganization parameter? 

    I am getting this error: The 'Value' expression for the text box 'PhysicianOrganization' refers to the field 'PhysicianOrganization'. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.
    ----------------------------
    The definition of the report '' is invalid.
    ----------------------------
    An error occurred during local report processing.

     

  • Karolina411's avatar
    Karolina411
    Icon for Helper V rankHelper V
    2 years ago

    This worked until I added the main dataset--do I need the PHYSORG and PCPLocation parameters referenced?I am getting this error: The parameter PhysicianOrganization is not referred in the query.
    ----------------------------
    Query execution failed for dataset 'dsPhysicianOrganizations'.
    ----------------------------
    An error has occurred during report processing.
    ----------------------------
    An error occurred during local report processing.

    which related to the main dataset:

    EVALUATE
    VAR FilteredTable =
    SUMMARIZECOLUMNS(
    'Providers'[PhysicianOrganization],
    'Providers'[PCPLocation],
    'Patients'[InsuranceType],
    'DateDim'[Year],
    'DateDim'[MonthNameAbbreviation],
    FILTER(
    ALL('Providers'[PhysicianOrganization]),
    'Providers'[PhysicianOrganization] = @PhysicianOrganization
    ),
    FILTER(
    ALL('Providers'[PCPLocation]),
    'Providers'[PCPLocation] = @PCPlocation
    ),
    FILTER(
    VALUES('DateDim'[Year]),
    'DateDim'[Year] = VALUE(@Year)
    ),
    FILTER(
    VALUES('DateDim'[MonthNameAbbreviation]),
    'DateDim'[MonthNameAbbreviation] = @Month
    ),
    FILTER(
    VALUES('Patients'[Populations]),
    'Patients'[Populations] = "HAP HFHS Employees"
    ),
    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
    )