Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
garythomannCoGC
Impactful Individual
Impactful Individual

Report Builder - DAX query working correctly in DaxStudio but not in report

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

Screenshot 2022-04-14 160922.pngScreenshot 2022-04-14 161002.pngScreenshot 2022-04-14 161033.png

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

 

1 ACCEPTED SOLUTION
garythomannCoGC
Impactful Individual
Impactful Individual

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.

View solution in original post

2 REPLIES 2
garythomannCoGC
Impactful Individual
Impactful Individual

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors