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
)
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.
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.
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
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
)
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.
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.
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.
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.
Thanks so much but it did not work and it is not giving me an error message. i will try again:
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.
Helpful resources
Power BI Monthly Update - September 2024
Check out the September 2024 Power BI update to learn about new features.
Microsoft Fabric & AI Learning Hackathon
Learn from experts, get hands-on experience, and win awesome prizes.
Fabric Community Update - September 2024
Find out what's new and trending in the Fabric Community.
