topic Re: Filtering with a sub filter with multiple value selection - will not work for Report Builder PR in Report Server

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

Karolina411 — Mon, 23 Sep 2024 00:56:28 GMT

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. (PhysicianOrganization must filter PCPLocation) Below the Physician Organization does not filter on the PCPLocation and it must!
Dataset1 will be filtered by PhysicianOrganization, PCPLocation and Date (monthly reporting) which used month and year and PhysicianLocation Dataset which has the Physician Organization & PCPLocation values which the Parameters connect to. Below is what the report data looks like and I added the DAX fields below (this is from a CUBE or Tabular connection) I am thinking I should put the Year and Month in the PhysicianLocation dataset?
I have 3 datasets that do the following:

Filter the PCPLocation by PhysicianOrganization but it will not currently allow me to choose multiple PCPLocations.

I have 3 tables---

Parameter short dataset:

EVALUATE

SUMMARIZECOLUMNS(

'Providers'[PCPLocation],

'DateDim'[Year],

'DateDim'[MonthNameAbbreviation],

FILTER(

'Providers',

'Providers'[PhysicianOrganization] = @PhysicianOrganization

FILTER(

'DateDim',

'DateDim'[Year] = VALUE(@Year) -- Convert @Year to integer

&& 'DateDim'[MonthNameAbbreviation] = @Month

)

) I think this DAX must be changed: PhysicianOrganization is assigned to the parameter but only

These parameters are listed here: @PhysicianOrganization, @Year, =@Month

2nd Short dataset: (no query parameters are added but the PCPLOCATION points to this dataset) I think this may be the issue a I cannot get multiple choices to work)

EVALUATE

SUMMARIZECOLUMNS(

'Providers'[PhysicianOrganization]

)

Here is dataset 3 which is the Main DATASET AND THE YEAR AND MONTH ARE HARD CODED but the other PhysicianOrganization and PCPLOcation get their Available values for the other 2 datasets. I do list all 4 filters in the dataset parameters

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] = "HAP HFHS Employees"

FILTER(

VALUES('Providers'[PCPLocation]),

'Providers'[PCPLocation] = @PCPLocation

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

)

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

Anonymous — Tue, 24 Sep 2024 07:51:26 GMT

Hi, @Karolina411

According to your description, I use create a datatset using the following SSAS data and filter it by parameter:

EVALUATE SUMMARIZE(FILTER('UserInfo','UserInfo'[Name]=@Username),UserInfo[Age],UserInfo[Income],UserInfo[Name])

As you write it, it will return blank when the parameter selects multiple values:

Single value:

Blank is returned when multiple values are selected:

We need to change the query statement to the following using RSCustomDaxFilter:

EVALUATE SUMMARIZECOLUMNS(UserInfo[Age],UserInfo[Income],UserInfo[Name], RSCustomDaxFilter(@Username,EqualToCondition,[UserInfo].[Name],String))

Then when we pass the multiselect parameter, it works:

You should change the parameters that you need to pass multiple values to use RSCustomDaxFilter to reference these parameters. You can refer to the following blog:

Chris Webb's BI Blog: Power BI Report Builder And RSCustomDaxFilter (crossjoin.co.uk)

Introduction to DAX for paginated reports - Simple Talk (red-gate.com)

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.

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

Karolina411 — Wed, 25 Sep 2024 03:02:17 GMT

Thank you but this did not work but I will read more about it.

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

Anonymous — Wed, 25 Sep 2024 05:40:08 GMT

Hi, @Karolina411

Thank you for your reply. In my last reply is an example, only use this Report Builder function, so that when our parameters select multiple choices, it will be correctly converted into a filter statement that the AS engine can accept.

You can refer to the following thread, which has the same requirement as yours:

Solved: Paginated Report - multiple parameter via DAX quer... - Microsoft Fabric Community

You can see the following discussion about this function:

Format Query doesn't like RSCustomDaxFilter · Issue #637 · DaxStudio/DaxStudio (github.com)

You need to use this function and then adjust your DAX query appropriately to support the correct display of content when using multiple values in Report Builder.

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.

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

Karolina411 — Wed, 25 Sep 2024 13:30:53 GMT

I will try again but this is my current problem:

PROBLEM---Filtering is not working for multiple values!

I am working on a Paginated report that the user would like to :

Select date (Month/Year) then the Physician organization then the pcp location to have a pdf for whatever the user selects only. Below the Physician Organization does NOT filter on the PCP Location.
Dataset1 (main dataset) will be filtered by Physician Organization, PCPlocation and Date (monthly reporting) and Year and Month values are what the Parameters connect to. Below is what the report data looks like and I added the DAX fields below (this is from a CUBE or Tabular connection) .

Below the filter ‘PhysicianOrganization’ is not filtering PCPLocation correctly.

I have a PO dataset and a PCPLocation dataset too. Below is the code:

The Physician Organization parameter is assigned to the PO dataset and the PCPlocation is assigned to the PCPLocation dataset. All have ‘multiple values’ checkmarked and no Default Values.

PO Dax query:

EVALUATE

DISTINCT(

SELECTCOLUMNS(

'Providers',

"PhysicianOrganization", 'Providers'[PhysicianOrganization]

)

PCPLocation dax query:

EVALUATE

VAR SelectedPhysicianOrg = VALUES('Providers'[PhysicianOrganization])

RETURN

SUMMARIZE(

FILTER(

'Providers',

'Providers'[PhysicianOrganization] IN SelectedPhysicianOrg

'Providers'[PCPLocation]

)

In report Builder I need to be able to select date (Month/Year) then the Physician Organization to filter on the PCPLocation . Below the Physician Organization does not filter on the PCP Location and I am getting a circular reference error.

Dataset 1 is here I have the @PCPLocation, @Year, and @Month parameters referenced here with ‘multiple values selected in the dataset description:

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('Providers'[PhysicianOrganization]),

'Providers'[PhysicianOrganization] = @PhysicianOrganization

FILTER(

VALUES('Providers'[PCPLocation]),

'Providers'[PCPLocation] = @PCPLocation

FILTER(

VALUES('Patients'[Populations]),

'Patients'[Populations] = "HAP HFHS Employees"

FILTER(

'Patients',

'Patients'[InsuranceType] IN {"CDHPPreferred", "Tiered"}

"UniquePatients", [UniquePatients],

"TotalCostYTD", [TotalCostYTD],

"MemberMonthsYTD", [MemberMonthsYTD],

"Admits1000YTD", [Admits1000YTD],

"ERVisitsYTD", [ERVisitsYTD],

"ERVisits1000YTD", [ERVisits1000YTD],

"BloodPressureNumerator", [BloodPressureNumerator],

"BloodPressureDenominator", [BloodPressureDenominator],

"BloodPressureRate", [BloodPressureRate],

"DiabetesHbA1cLessThan8Numerator", [DiabetesHbA1cLessThan8Numerator],

"DiabetesDenominator", [DiabetesDenominator],

"DiabetesHbA1cLessThan8Rate", [DiabetesHbA1cLessThan8Rate],

"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],

"FuInNumerator",[FuInNumerator],

"FuInDenominator",[FuInDenominator],

"F/U Hospitalization for Mental Illness",[F/U Hospitalization for Mental Illness],

"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],

"September 2023 YTD", "September 2023 YTD",

"Zero", 0

)

VAR FilteredResult =

FILTER(

FilteredTable,

[UniquePatients] <> 0

)

RETURN

FilteredResult

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

Anonymous — Thu, 26 Sep 2024 04:43:32 GMT

Hi, @Karolina411

Thank you very much for your reply. I use the following AS data similar to yours, which you can apply to your dataset by following the steps below:

First, use the following DAX expression to create a PO Dataset:

EVALUATE DISTINCT ( SELECTCOLUMNS ( 'Providers', "PhysicianOrganization", 'Providers'[PhysicianOrganization] ) )

Next, create a parameter for it:

Then create a second dataset using the following DAX expression - PCPLocation:

Before creating this Dataset, we need to bind it to the parameters we created earlier:

Then you need to enter the following expression:

EVALUATE SUMMARIZECOLUMNS ( 'Providers'[PCPLocation], RSCustomDaxFilter ( @PhysicianOrganization, EqualToCondition, [Providers].[PhysicianOrganization], String ) )

After entering the expression, we select a data source for it:

Please be careful not to select the data source first and then enter the expression, as this will result in an error.

Next, create a new PCPlocation parameter:

Next, create a new Year dataset and set it as a parameter:

Similarly, set up a Month dataset:

The last step is to set up our main dataset. In this step, we still need to bind parameters first >> enter expression >> select data source. This order cannot be changed:

Here are the results:

When I don't select any organization, I can't select the region:

When I select one of these organizations, my region gets filtered correctly:

When I select multiple organizations, my regions will also dynamically update and can be correctly filtered by organization:

Below is my DAX query for dataset1:

EVALUATE SUMMARIZECOLUMNS ( 'Providers'[PhysicianOrganization], 'Providers'[PCPLocation], 'Patients'[InsuranceType], 'DateDim'[Year], 'DateDim'[MonthNameAbbreviation], RSCustomDaxFilter ( @PhysicianOrganization, EqualToCondition, [Providers].[PhysicianOrganization], String ), RSCustomDaxFilter ( @PCPlocation, EqualToCondition, [Providers].[PCPLocation], String ), FILTER ( VALUES ( 'DateDim'[Year] ), 'DateDim'[Year] = VALUE(@Year) ), FILTER ( VALUES ( 'DateDim'[MonthNameAbbreviation] ), 'DateDim'[MonthNameAbbreviation] = @Month ) )

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.

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

Anonymous — Thu, 26 Sep 2024 04:38:40 GMT

Hi, @Karolina411

Thank you very much for your reply. I use the following AS data similar to yours, which you can apply to your dataset by following the steps below:

First, use the following DAX expression to create a PO Dataset:

EVALUATE DISTINCT ( SELECTCOLUMNS ( 'Providers', "PhysicianOrganization", 'Providers'[PhysicianOrganization] ) )

Next, create a parameter for it:

Then create a second dataset using the following DAX expression - PCPLocation:

Before creating this Dataset, we need to bind it to the parameters we created earlier:

Then you need to enter the following expression:

EVALUATE SUMMARIZECOLUMNS ( 'Providers'[PCPLocation], RSCustomDaxFilter ( @PhysicianOrganization, EqualToCondition, [Providers].[PhysicianOrganization], String ) )

After entering the expression, we select a data source for it:

Please be careful not to select the data source first and then enter the expression, as this will result in an error.

Next, create a new PCPlocation parameter:

Next, create a new Year dataset and set it as a parameter:

Similarly, set up a Month dataset:

The last step is to set up our main dataset. In this step, we still need to bind parameters first >> enter expression >> select data source. This order cannot be changed:

Here are the results:

When I don't select any organization, I can't select the region:

When I select one of these organizations, my region gets filtered correctly:

When I select multiple organizations, my regions will also dynamically update and can be correctly filtered by organization:

Below is my DAX query for dataset1:

EVALUATE SUMMARIZECOLUMNS ( 'Providers'[PhysicianOrganization], 'Providers'[PCPLocation], 'Patients'[InsuranceType], 'DateDim'[Year], 'DateDim'[MonthNameAbbreviation], RSCustomDaxFilter ( @PhysicianOrganization, EqualToCondition, [Providers].[PhysicianOrganization], String ), RSCustomDaxFilter ( @PCPlocation, EqualToCondition, [Providers].[PCPLocation], String ), FILTER ( VALUES ( 'DateDim'[Year] ), 'DateDim'[Year] = @Year ), FILTER ( VALUES ( 'DateDim'[MonthNameAbbreviation] ), 'DateDim'[MonthNameAbbreviation] = @Month ) )

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.

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

Karolina411 — Thu, 26 Sep 2024 17:23:59 GMT

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

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

Anonymous — Fri, 27 Sep 2024 08:34:25 GMT

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.

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

Karolina411 — Wed, 02 Oct 2024 21:05:16 GMT

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

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

Karolina411 — Wed, 02 Oct 2024 21:38:17 GMT

@Anonymous I am stuck here:

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

Karolina411 — Thu, 03 Oct 2024 21:28:48 GMT

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.

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

Karolina411 — Thu, 03 Oct 2024 21:40:04 GMT

Okay --so I got PO and PCPlocation to filter. So I guess I then add this as my MAIN dataset to see the values?

EVALUATE

SUMMARIZECOLUMNS (

'Providers'[PhysicianOrganization],

'Providers'[PCPLocation],

'Patients'[InsuranceType],

'DateDim'[Year],

'DateDim'[MonthNameAbbreviation],

RSCustomDaxFilter (

@PhysicianOrganization,

EqualToCondition,

[Providers].[PhysicianOrganization],

String

RSCustomDaxFilter (

@PCPlocation,

EqualToCondition,

[Providers].[PCPLocation],

String

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

)

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

Karolina411 — Thu, 03 Oct 2024 22:50:37 GMT

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
)

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

Karolina411 — Fri, 04 Oct 2024 12:28:15 GMT

I will resubmit my issue step by step and see what I am missing.

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

Karolina411 — Fri, 04 Oct 2024 16:03:22 GMT

hi. I updated my current problem and am working with what I have:

Can you help me filter multiple values in both PhysicianOrganization and then PCPLocation 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 and others only. (PhysicianOrganization must filter PCPLocation) Below the Physician Organization does not filter on the PCPLocation and it a must!
Dataset1 will be filtered by PhysicianOrganization, PCPLocation and Date (monthly reporting) which used month and year and PhysicianLocation Dataset which has the Physician Organization & PCPLocation values which the Parameters connect to. Below is what the report data looks like and I added the DAX fields below (this is from a CUBE or Tabular connection) I am thinking I should put the Year and Month in the PhysicianLocation dataset?

I have 3 datasets that do the following:

Filter the PCPLocation by PhysicianOrganization but when I run my report I do not see data:

I’ve 3 datasets:

PO – First Filter
PCPLocation – 2nd filter (I am using RSCustomDaxFilter(@ProvidersPhysicianOrganization,EqualToCondition)
Main Dataset which has the month/year parameter

My DAX Code is below:

4 Parameters 1) PO Parameter Referencing Query 1

EVALUATE

DISTINCT (

SELECTCOLUMNS (

'Providers',

"PhysicianOrganization", 'Providers'[PhysicianOrganization]

)

PCPlocation Dataset (2nd filter)

2) EVALUATE SUMMARIZECOLUMNS('Providers'[PCPLocation], RSCustomDaxFilter(@ProvidersPhysicianOrganization,EqualToCondition,[Providers].[PhysicianOrganization],String))

Above references Physician Organization

Finally the main dataset where I THINK the issue MAY LIE:

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] = "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

)

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

Anonymous — Mon, 07 Oct 2024 02:56:01 GMT

Hi, @Karolina411

Thank you for your reply. Your previous PO and PCPlocation work fine, which is good. The query required for your main dataset should be similar to the following:

EVALUATE SUMMARIZECOLUMNS('Providers'[PhysicianOrganization],'Providers'[PCPLocation], 'Patients'[InsuranceType],'DateDim'[Year], 'DateDim'[MonthNameAbbreviation],RSCustomDaxFilter(@PhysicianOrganization,EqualToCondition,[Providers].[PhysicianOrganization],String), RSCustomDaxFilter(@PCPlocation,EqualToCondition,[Providers].[PCPLocation],String), FILTER( VALUES('DateDim'[Year]), 'DateDim'[Year] = VALUE(@Year) ), FILTER( VALUES('DateDim'[MonthNameAbbreviation]), 'DateDim'[MonthNameAbbreviation] = @Month ) )

Please note that I also used RSCustomDaxFilter in the main dataset to obtain PO and PCPlocation. Based on my DAX expression, you can continue to add your subsequent measures in SUMMARIZECOLUMNS.
Here is my demonstration result:

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.