4 Filters for Values using DAX in Report Builder

Karolina411 — Sat, 21 Sep 2024 19:13:09 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 two tables--- this contains 2 the PhysicianOrganization and PCPLocation parameter that are referenced in the

Parameter short dataset:

EVALUATE SUMMARIZECOLUMNS(

'Providers'[PhysicianOrganization]

,'Providers'[PCPLocation] )

The main dataset that has all the values but PhysicianOrganization and PCPLocation have the parameter referenced but that points to the short 1) table above. Only the Hand filled in Parameters for Month and Year (all hand filled in) are in the main dataset.
How can I get what is needed? I just need the report to be able to 1st filter by PhysicianOrganization then PCPLocation (subset of the PhysicianOrganization column).

Here is Dataset1 where all 4 parameters are referenced :

EVALUATE

SUMMARIZECOLUMNS(

'Providers'[PCPLocation],

'Providers'[PhysicianOrganization],

'Patients'[InsuranceType],

'DateDim'[Year],

'DateDim'[MonthNameAbbreviation],

FILTER(

'DateDim',

'DateDim'[Year] = VALUE(@Year)

FILTER(

'Providers',

'Providers'[PhysicianOrganization] = @PhysicianOrganization

&& 'Providers'[PCPLocation] = @PCPLocation

FILTER(

'DateDim',

'DateDim'[MonthNameAbbreviation] = @Month

FILTER(

'Patients',

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

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

"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

)

Re: 4 Filters for Values using DAX in Report Builder

Anonymous — Tue, 24 Sep 2024 02:04:07 GMT

Hi, Karolina411

I am glad to help you.

May I ask what type of data source you are using?

And regarding the following parameters, may I ask how you get the parameter values?

Could you please provide us with some simple test data or pbix files (exclude sensitive data) for us to test? These will help us to be able to understand your requirements more clearly and solve the problems you are experiencing.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: 4 Filters for Values using DAX in Report Builder

Karolina411 — Mon, 23 Sep 2024 13:14:36 GMT

This data is coming from our analysis server and it is a tabular model. The year and month are hand entered to match the date columns and the PCPLocation and Physician Org are coming from the Tabular model. I thought checkmarking - multiple values in parameters would work with this data but I guess not.

topic 4 Filters for Values using DAX in Report Builder in Report Server

4 Filters for Values using DAX in Report Builder

Re: 4 Filters for Values using DAX in Report Builder

Re: 4 Filters for Values using DAX in Report Builder