Forum Discussion
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. (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
)
16 Replies
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.
Anonymous I am stuck here:
Okay --so I got PO and PCPlocation to filter. So I guess I then add this as my MAIN dataset to see the values?
EVALUATESUMMARIZECOLUMNS ('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"))RETURNADDCOLUMNS(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)I will resubmit my issue step by step and see what I am missing.
Thank you but this did not work but I will read more about it.
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.
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 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.
