Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Report Builder issue. DAX query gives correct results when run in DaxStudio. But when the DAX query is plugged into Report Builder we get incorrect results.
Multiple parameters but lets just concentrate on the simplest parameter. IOP only has 3 values; blank, No, Yes.
Any combination we are fine in DaxStudio.
Report Builder land...
277 records in total. IOP=NA gives 13 records. IOP=No gives 263. IOP=Yes gives 1. 263+13+1=277.
IOP=NA, No, Yes gives 263 ?
IOP=NA, No gives 0 ?
IOP=NA, Yes gives 0 ?
IOP=No, Yes gives 0 ?
DAX code segments...
EVALUATE
-- parse the parameter value lists ready for the main querys
VAR IncludeInOperationalPlan = SUBSTITUTE (TRIM (SUBSTITUTE (@IncludeInOperationalPlan, ",", "|")), "| ", "|" )
-- build the lists
VAR PAY1 =
FILTER (
SELECTCOLUMNS (
"table",
"columns"
),
-- Include In Operational Plan
SWITCH (
TRUE (),
LEN (IncludeInOperationalPlan) = 0, TRUE (), -- ignore
--PATHCONTAINS (IncludeInOperationalPlan, "All"), TRUE (), -- ignore
IF ( PATHCONTAINS ( IncludeInOperationalPlan, "NA" ), -- NA parameter selection
IF ( [Include In Operational Plan] IN
{"NA",
"0",
"Not Applicable",
"Select",
" ",
"",
BLANK()},
TRUE (), -- NA value found
IF ( PATHCONTAINS ( IncludeInOperationalPlan, [Include In Operational Plan] ),
TRUE (), -- direct match found
FALSE () -- out of scope condition
)
),
FALSE ()
),
TRUE (),
PATHCONTAINS ( IncludeInOperationalPlan, [Include In Operational Plan] ), TRUE (),
FALSE ()
)
IOP parameter setup looks normal...
Aware of "allow blank value" parater property but found it has no effect as outlined in my previous post below.
Did try with it set again today to make sure but has no effect so leaving it unchecked.
power bi report builder - parameter property - "allow blank value"
Note also using DaxStudio I have checked the conversion preparation of the parameter values for the pathcontains usages and looking fine ie NA|No|Yes etc
Solved! Go to Solution.
Doh! Caught by this one again.
=Join(Parameters!IncludeOperationPlan.Value,"|")
Added to the Dataset Properties > Parameters > Parameter Value (expression) resolves the issue.
The Parameters!<parm> is an array. The Join function is needed to convert and delimit the array into a string to be passed in to the dax query.
Doh! Caught by this one again.
=Join(Parameters!IncludeOperationPlan.Value,"|")
Added to the Dataset Properties > Parameters > Parameter Value (expression) resolves the issue.
The Parameters!<parm> is an array. The Join function is needed to convert and delimit the array into a string to be passed in to the dax query.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.