Forum Discussion
Filtering with a sub filter with multiple value selection - will not work for Report Builder PR
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
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.