Forum Discussion
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:
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
- Jaywant-ThoratSuper User
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