Multi-Choice Filters within a Tabular Model in Report Builder problem

[Karolina411]

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:

1. 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

    )

[Karolina411]

@v-jianpeng-msft I am getting this error: 

TITLE: Power BI Report Builder
------------------------------

Query preparation failed.

------------------------------
ADDITIONAL INFORMATION:

RSCustomDaxFilter is not supported in text mode. (MDXQueryGenerator)

------------------------------

Object reference not set to an instance of an object. (MDXQueryGenerator)

------------------------------
BUTTONS:

OK
------------------------------

[v-jianpeng-msft · Accepted solution]

Hi, @Karolina411 

Thank you for your reply. Your current error is because you used the RSCustomDaxFilter function incorrectly. You can learn how it works from the following two articles:

Solved: RSCustomDaxFilter Documentation - Microsoft Fabric Community

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.

[v-jianpeng-msft]

Hi,  @Karolina411 

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.