report builder results sometimes differ to that of dax query

[garythomannCoGC]

Having testing issues with a Report Builder paginated report.  Thought that my copy of the dataset was the issue and downloaded again to make sure.

Situation like this always suggest a data issue. 

Or the programmer (me) has done something brain dead :}. 

Or could also be that the report builder treats dax queries differently but that would be ‘not nice’. 

Or something else?

DAX query run in dax studio to local copy of dataset downloaded from the pbi service.

Code portion relating to parameter IOP (Include Operation Plan)

        -- Include Operation Plan
        SWITCH (
            TRUE (),
            LEN (IncludeOperationPlan) = 0,             TRUE (),   -- ignore
            PATHCONTAINS (IncludeOperationPlan, "All"), TRUE (),   -- ignore

            IF ( PATHCONTAINS ( IncludeOperationPlan, "NA" ),   -- NA parameter selection
                IF ( [Include Operation Plan] IN 
                         {"NA",
                          "0",
                          "Not Applicable",
                          "Select",
                          " ", 
                          "",  
                          BLANK()}, 
                     TRUE (),   -- NA value found
                     IF ( PATHCONTAINS ( IncludeOperationPlan, [Include Operation Plan] ), 
                          TRUE (),   -- direct match found
                          FALSE ()   -- out of scope condition
                     ) 
                ),
                FALSE ()   
            ),
            TRUE (),

            PATHCONTAINS ( IncludeOperationPlan, [Include Operation Plan] ), TRUE (),
            FALSE ()   
        ) &&   

Full DAX query link below

Project Progress List.dax.sql 

Scenarios

We have two scenarios (1. and 2.) to test as per below.  For all tests they work in the query but sometimes not in the report.

Each line represents a test run.  Parameter values right to left as per the report, hence six sets of parameter values.

IOP = Include Operation Plan.   Set {} means more than one value chosen.  All parameters are multi-value.

Also I am using "All" as a work around to select all possible parameter values enabling the user to bypass Select All option which will select/return a lot of values.

main query                                          report

1. ce, All, 20-21, Q4, Yes, Yes = 1 row            same
2. ce, All, 20-21, Q4, No, Yes  = 1 row            same
3. ce, All, 20-21, Q4, {No,Yes}, Yes = 2 rows      0 rows why?

                                      (project#)

    IOP only: Yes                   = 2421          1 row

    IOP only: No                    = 3546          1 row

    IOP only: No,Yes                = 2421 3546     0 rows      why?

    IOP only: NA,No,Yes             = as above      3546        why?  2421 missing?           

    IOP only: All,NA,No,Yes         = as above      3546        why?  2421 missing?

    IOP only: All                   = as above      2421 3546

1. co, All, 21-22, Q2, Yes, Yes = 1 row            same        3189
2. co, All, 21-22, Q2, No, Yes  = 6 rows           same
3. co, All, 21-22, Q2, {No,Yes}, Yes = 7 rows      0 rows      why?

                                      (project#)

    IOP only: Yes                   = 3189          same

    IOP only: No                    = 6 rows        6 rows      3233 3344 3382 3383 3392 3404

    IOP only: No,Yes                = 7 rows        0 rows      why?

    IOP only: NA,No,Yes             = as above      6 rows      why?  3189 missing?  

    IOP only: All,NA,No,Yes         = as above      6 rows      why?  3189 missing?

    IOP only: All                   = as above      7 rows

[Aaron_C]

Hey @garythomannCoGC , 

I'm hitting an absolute wall with this Query Designer bug and stumbled upon this post while trying to solve it.


Also noticed that the Statement and Command Text are completely out of sync when inspecting the JSON of my .rdl

What was the ultimate resolution? Can I solve this natively, or do I have to edit the actual JSON?

[garythomannCoGC]

Hi @Aaron_C  the issue arises when parameters are changed.  So the 'internal' QD version of the query becomes out of date and issues.  First sign will be an error pointing to your first parameter in the list.  Just look at all your parameter setups to find the issue.  No need for JSON edit.  Just make sure your 'internal' QD query works in daxstudio.

[Aaron_C]

Hey Gary, Thanks for the reply!

Yeah what I found is that the internal QD queries for some of them still had parameters active inside, but trying to clear these effectively killed the whole query.

Solution after much trial and error was just to delete the old Datasets completely and re-write them fresh using the desired query!

Took a while to get there thought!

[garythomannCoGC · Accepted solution]

Day 2.  Recheck all the querys  ie the QD versions vs the DP version.  Note QD can also have its own default parameter values so check that these too are all the same!

Nope still not working for multiple parameter values.  Check the rdl source via vim and we see that the parameter querys are still old even though from RB we see the right query in QD. 

    <DataSet Name="FinancialYear">
      <Query>
        <DataSourceName>Governance_EnterprisePortfolioManagement</DataSourceName>
        <rd:DesignerState>
          <QueryDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition">
            <CommandType>DAX</CommandType>
            <Type>Query</Type>
            <QuerySpecification xsi:type="MDXQuerySpecification">
              <Select>
                <Items>
                  <Item>
                    <ID xsi:type="Level">
                      <DimensionName>Project Progress List</DimensionName>
                      <HierarchyName>Financial Year</HierarchyName>
                      <HierarchyUniqueName>[Project Progress List].[Financial Year]</HierarchyUniqueName>
                      <LevelName>Financial Year</LevelName>
                      <UniqueName>[Project Progress List].[Financial Year].[Financial Year]</UniqueName>
                    </ID>
                    <ItemCaption>Financial Year</ItemCaption>
                  </Item>
                </Items>
              </Select>
              <AutoExecute>false</AutoExecute>
              <From>Model</From>
              <Filter>
                <FilterItems />
              </Filter>
              <Calculations />
              <Aggregates />
              <QueryProperties />
            </QuerySpecification>
            <Query>
              <Statement>EVALUATE
DISTINCT (
    FILTER (
        VALUES ( 'Project Progress List'[Financial Year] ),
        LEN ( 'Project Progress List'[Financial Year] ) &gt; 0   // remove blanks
    )
)
ORDER BY [Financial Year] ASC
</Statement>
              <ParameterDefinitions />
            </Query>
          </QueryDefinition>
        </rd:DesignerState>
        <CommandText>-- 202201   gt  no DAX UDF yet, hardcode for each usage   Each parameter code block is a clone.

DEFINE
    -- create summary table
    VAR SummaryTableUnique = 
        -- distinct list of values
        FILTER (
            DISTINCT (
                VALUES ( 'Project Progress List'[Financial Year] )
            ),
            NOT [Financial Year] IN {
                "NA",
                "0",
                "Not Applicable",
                "Select",
                " ", 
                "",  
                BLANK()
            }   
        )
    -- add sorting column
    VAR SummaryTable = 
        ADDCOLUMNS (
            SummaryTableUnique, 
            "Sort", 1
        )

    -- create All and NA rows
    VAR AllTable =
        DATATABLE (
            "Financial Year", STRING,
            "Sort", INTEGER,
            {
                { "All", 0 }
            }
        )
    VAR NATable =
        DATATABLE (
            "Financial Year", STRING,
            "Sort", INTEGER,
            {
                { "NA", 0 }
            }
        )

    -- sandwich together
    VAR UnionTable1 = UNION ( SummaryTable, AllTable )   -- keep data lineage by doing SummaryTable first 
    VAR UnionTable2 = UNION ( UnionTable1, NATable )    

EVALUATE
    UnionTable2

ORDER BY 
    [Sort] ASC,   -- sort All, NA first then all others
    [Financial Year] ASC
</CommandText>
      </Query>
      <Fields>
        <Field Name="Financial_Year">
          <rd:TypeName>System.String</rd:TypeName>
          <DataField>Project Progress List[Financial Year]</DataField>
        </Field>
        <Field Name="Sort">
          <rd:TypeName>System.Int64</rd:TypeName>
          <DataField>[Sort]</DataField>
        </Field>
      </Fields>
    </DataSet>

All the parameter querys are clones.  From the code portion above we see that <Query><Statement> is the old code and <rd:DesignerState> <CommandText>  is the new query.

'Failed to open report' error, mismatched tags.  

Can the <rd:DesignerState> portion just be deleted ?   Go again. Lets try.  Much easier than cut n paste of query code.

Opens up in RB without error.  Lets run it.  Works!  A lot of xml code was scapled out!

But still same issue with multi-value parameters not working.  Doh! 

Wonder if the <rd:DesignerState> code tags were reinstated on load even though I did not touch QD?  =no

What if I try to use QD?  Yes QD will load and displays the right query code.  Lets cancel out of QD.

Note also that Validate Query can torpedo the field list and blow away the parameters when things get more complex, don't use.  Stick to dax studio.  

Back to the original issue.

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

Again how do we see the actual string that is passed in from the report to the dax query at run time?

[garythomannCoGC]

Thanks Ibendlin for your thoughts and reply.  But the query actually works for the tests and does what I want it to do.

I vimdiff'd my copy of the dax query against a copy out of the report and they are exactly the same except for the parameter tests in xml at the end of my working copy.

Could the pathing somehow be at fault from the dax query in the report?   I agree that the code portion you highlighted looks a bit off.  NA and All are both my arbitrary parameter values but needed to drive this the way I want.  Looks great from the front end and the user perspective with less confusion which is what is needed.

But NA is also a real value within the database.   Hence hitting All and NA first before the real value cross polination.

Could it be a difference in the environments?  Like say how upper and lowercase is treated.  Say dax studio and report builder treat dax queries differently; one is case insensitive whereas the other is case sensitive.  

Letter case-sensitivity in DAX, Power BI and Analysis Services 

M is insensitive whereas DAX is not.

Looking at parameter delimiter.  The dax query converts from comma to "|".  I can output the parameter to the report but can we show a variable from the dax out to the report post processing?

Does dax have an output processing function?  ie push text to a flat file.

I suppose I am looking for a debugger too for RB?

VAR IncludeOperationPlan = SUBSTITUTE (TRIM (SUBSTITUTE (@IncludeOperationPlan, ",", "|")), "| ", "|" )

But this works in dax studio to convert comma spaces. 

Open the rdl up in vim and look for any previous bits of code.

Can I load the rdl up in ssrs so I can do reverse lookups of objects?  I suppose so lets do it!  [nope no can do, can't install without admin rights, submit request and wait for days]

Continue with manual cross reference of vim exposed rdl file and looking from the RB front end.

Weirdville, I am seeing two sets of the dax query within the rdl source!

One is an old version and my current version, well that would do it maybe.  Now to figure out wtf is going on.

From the front end the main query is different when viewed either via the Dataset Properties (right mouse menu) or the Query Designer.  Lol I thought that these were supposed to be in sync.  I hardly ever use the Query Designer because it 'works over' the column names and takes too long to load.

How to bring these two querys back in sync?  Nothing on the internet so far.  In Query Designer copy and pasted the right query back in here.  Hit the Prepare Query button first then try execute.  No errors lets go OK.  Seems to have taken the  right query.

Now lets check via Dataset Properties, Query Designer and vim rdl source.  And of course run it!

[garythomannCoGC]

Query Designer is a bug and should be removed/commented out 

Vote to have QD removed from the m$ code base.

[lbendlin]

This part

IF ( PATHCONTAINS ( IncludeOperationPlan, "NA" ),   -- NA parameter selection
                IF ( [Include Operation Plan] IN 
                         {"NA",
                          "0",
                          "Not Applicable",
                          "Select",
                          " ", 
                          "",  
                          BLANK()},

makes little sense.  I would propose you move the exception tests further up. Is IncludeOperationPlan actually a path variable with pipe separators or are you reinventing the SEARCH function?