Forum Discussion

GeorgeColl's avatar
GeorgeColl
Helper II
6 months ago
Solved

DAX Query - Blank Result from Query when both parameters have multiple values

When I run my paginated report and provide the parameters I get the following error:

 

The 'HiddenInsurerParam' parameter is missing a value.
 
Below is my code, the idea is to create a distinct list of insurers from two different tables 'CAClaims' and 'Corsair Policy Years'. The @insurer and @insuredName parameters filter the 'CAClaims' table and @CorsairInsuredName filters 'Corsair Policy Years'. I then want a distinct list of the insurers from each, so I'm taking the union over the two filtered tables.
 
When I only provide one value for either @insuredName or @CorsairInsuredName it works fine, however when I put two values into each I get the missing value error and I can't figure out why. CoPilot expected it was an issue with pathitem, but it wasn't able to assist in providing a workaround.
 
The parameters will be a list of strings selected by the user from a list coming from the database.
 
DEFINE

// --- Normalise parameter delimiters: replace "," with "|" ---
VAR _Insurer =
    SUBSTITUTE(@Insurer, ",", "|")

VAR _InsuredName =
    SUBSTITUTE(@insuredName, ",", "|")

VAR _CorsairInsuredName =
    SUBSTITUTE(@CorsairInsuredName, ",", "|")

// --- Split parameters into tables using PATHITEM on cleaned strings ---
VAR InsurerList =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES(1, PATHLENGTH(_Insurer), 1),
            "InsList", PATHITEM(_Insurer, [Value])
        ),
        "Insurer", [InsList]
    )

VAR InsuredNameList =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES(1, PATHLENGTH(_InsuredName), 1),
            "InsName", PATHITEM(_InsuredName, [Value])
        ),
        "InsuredName", [InsName]
    )

VAR CorsairInsuredNameList =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES(1, PATHLENGTH(_CorsairInsuredName), 1),
            "CorsInsName", PATHITEM(_CorsairInsuredName, [Value])
        ),
        "CorsairInsuredName", [CorsInsName]
    )

// --- Flags: only apply a filter if the parameter list is non-empty ---
VAR HasInsurerFilter           = COUNTROWS(InsurerList) > 0
VAR HasInsuredFilter           = COUNTROWS(InsuredNameList) > 0
VAR HasCorsairInsuredFilter    = COUNTROWS(CorsairInsuredNameList) > 0

// --- Apply parameter-aware filters to each source table ---
VAR CAClaimsFiltered =
    FILTER (
        'CAClaims',
        // Insurer filter (on CAClaims) if provided
        (NOT HasInsurerFilter
         || SUMX (
                InsurerList,
                INT(
                    CONTAINSSTRING(
                        'CAClaims'[Insurers],
                        [Insurer]
                    )
                )
            ) > 0
        )
        &&
        // Insured name filter (on CAClaims) if provided
        (NOT HasInsuredFilter
         || SUMX (
                InsuredNameList,
                INT(
                    CONTAINSSTRING(
                        'CAClaims'[Insured Name Simplification],
                        [InsuredName]
                    )
                )
            ) > 0
        )
    )

VAR CorsairFiltered =
    FILTER (
        'Corsair Policy Years',
        // Assured filter (on Corsair) if provided
        (NOT HasCorsairInsuredFilter
         || SUMX (
                CorsairInsuredNameList,
                INT(
                    CONTAINSSTRING(
                        'Corsair Policy Years'[Assured],
                        [CorsairInsuredName]
                    )
                )
            ) > 0
        )
    )

// --- Build the distinct union of insurers ---
VAR AllInsurers =
    DISTINCT (
        UNION (
            SELECTCOLUMNS(CAClaimsFiltered, "Insurers", 'CAClaims'[Insurers]),
            SELECTCOLUMNS(CorsairFiltered, "Insurers", 'Corsair Policy Years'[Insurers])
        )
    )

EVALUATE
AllInsurers
ORDER BY [Insurers]

 

  • This is not a PATHITEM issue. 

    The error: “The 'HiddenInsurerParam' parameter is missing a value.”

    is a Paginated Report parameter binding issue, not a DAX logic issue.

    Why It Breaks Only When Both Have Multiple Values

    When parameters allow multi-select, Paginated Reports:

    • Do not pass a single comma-separated string
    • They pass a multi-value parameter array

    But your DAX assumes @Insurer, @insuredName, @CorsairInsuredName are single text values.

    When multiple values are selected:

    • SSRS tries to map them
    • HiddenInsurerParam expects a value
    • It fails because multi-value parameters must be handled differently

    The Real Problem

    Your DAX expects: @Insurer = "A,B"

    But Paginated sends: @Insurer = { "A", "B" }

    Those are NOT the same thing.

    When two parameters both contain multiple values, the hidden parameter mapping breaks.

     

    Correct Way to Handle Multi-Value Parameters in Paginated + DAX, You must:

    1) In Report Builder

    Set parameter: Allow multiple values = TRUE

    2) In Dataset Parameter Mapping

    Map it like this: =JOIN(Parameters!Insurer.Value, "|")

    NOT directly: =Parameters!Insurer.Value


    Do this for all multi-value parameters.

    Example:

    @Insurer → =JOIN(Parameters!Insurer.Value, "|")
    @insuredName → =JOIN(Parameters!insuredName.Value, "|")
    @CorsairInsuredName → =JOIN(Parameters!CorsairInsuredName.Value, "|")

     

    Why This Fix Works

    Now DAX receives: A | B | C

    Which works perfectly with:

    PATHLENGTH()
    PATHITEM()

     

    Your DAX is fine. The issue is Multi-value parameters are not joined before being passed to DAX.

    Fix dataset parameter mapping using: 

    =JOIN(Parameters!ParameterName.Value, "|")

    and the error will disappear.

    =================================================================
    Did I answer your question? Mark my post as a solution! This will help others on the forum!

    Appreciate your Kudos!!

    Jaywant Thorat | MCT | Data Analytics Coach | SuperUser
    LinkedIn: https://www.linkedin.com/in/jaywantthorat/
    Join #MissionPowerBIBharat: https://tinyurl.com/JoinMissionPowerBIBharat
    #MissionPowerBIBharat
    LIVE with Jaywant Thorat

1 Reply

  • This is not a PATHITEM issue. 

    The error: “The 'HiddenInsurerParam' parameter is missing a value.”

    is a Paginated Report parameter binding issue, not a DAX logic issue.

    Why It Breaks Only When Both Have Multiple Values

    When parameters allow multi-select, Paginated Reports:

    • Do not pass a single comma-separated string
    • They pass a multi-value parameter array

    But your DAX assumes @Insurer, @insuredName, @CorsairInsuredName are single text values.

    When multiple values are selected:

    • SSRS tries to map them
    • HiddenInsurerParam expects a value
    • It fails because multi-value parameters must be handled differently

    The Real Problem

    Your DAX expects: @Insurer = "A,B"

    But Paginated sends: @Insurer = { "A", "B" }

    Those are NOT the same thing.

    When two parameters both contain multiple values, the hidden parameter mapping breaks.

     

    Correct Way to Handle Multi-Value Parameters in Paginated + DAX, You must:

    1) In Report Builder

    Set parameter: Allow multiple values = TRUE

    2) In Dataset Parameter Mapping

    Map it like this: =JOIN(Parameters!Insurer.Value, "|")

    NOT directly: =Parameters!Insurer.Value


    Do this for all multi-value parameters.

    Example:

    @Insurer → =JOIN(Parameters!Insurer.Value, "|")
    @insuredName → =JOIN(Parameters!insuredName.Value, "|")
    @CorsairInsuredName → =JOIN(Parameters!CorsairInsuredName.Value, "|")

     

    Why This Fix Works

    Now DAX receives: A | B | C

    Which works perfectly with:

    PATHLENGTH()
    PATHITEM()

     

    Your DAX is fine. The issue is Multi-value parameters are not joined before being passed to DAX.

    Fix dataset parameter mapping using: 

    =JOIN(Parameters!ParameterName.Value, "|")

    and the error will disappear.

    =================================================================
    Did I answer your question? Mark my post as a solution! This will help others on the forum!

    Appreciate your Kudos!!

    Jaywant Thorat | MCT | Data Analytics Coach | SuperUser
    LinkedIn: https://www.linkedin.com/in/jaywantthorat/
    Join #MissionPowerBIBharat: https://tinyurl.com/JoinMissionPowerBIBharat
    #MissionPowerBIBharat
    LIVE with Jaywant Thorat