Forum Discussion

Karolina411's avatar
Karolina411
Helper V
1 year 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
Anonymous's avatar
Anonymous
Not applicable
1 year 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
Helper V
1 year 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
)