Forum Discussion

Helper II

6 years ago

Parameters in Report Builder (DAX query copied from Performance Analyzer)

Hi - I'm building my first report in Power BI Report Builder and followed instructions to copy my query from Performance Analyzer. The query works as expected and it' s a huge time saver to copy it ...

Need Help

Anonymous

Not applicable

5 years ago

Hi - I am kind of in the similar scenario copying the query generated from Performance Analyzer. However it is restricting the number of records to 501 (TOPN function). When I delete it, I get an error Query Preparation Failed - The end of the input was reached(Microsoft Analysis Services). What am I doing icorrectly here? Am I deleting more than what should be deleted? If so please point out what has to be removed from the query below so it fetches all the existing records.

Thanks!!!

Query copied from Performance Analyzer. I have deleted the entire TOPN function (colored in red).

// DAX Query
DEFINE
VAR __DS0Core =
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'Query1'[Invoice_Complete],
'Query1'[Invoiced_Quantity],
'Query1'[Staples_order_no],
'Query1'[Staples_return_order_no],
'Query1'[return_status_description],
'Query1'[customer_name],
'Query1'[External_Document_No],
'Query1'[Our_Item_No],
'Query1'[EXTERNAL_ITEM_NO],
'Query1'[Return_Node],
'Query1'[Create_date],
'Query1'[return_create_date],
'Query1'[return_date_plus18days],
'Query1'[Open_days],
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Year],
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Quarter],
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[QuarterNo],
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Month],
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[MonthNo],
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Day],
"CountRowsQuery1", CALCULATE(COUNTROWS('Query1'))
)
),
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
NOT(ISBLANK('Query1'[Invoice_Complete])),
NOT(ISBLANK('Query1'[Invoiced_Quantity]))
),
NOT(ISBLANK('Query1'[Staples_order_no]))
),
NOT(ISBLANK('Query1'[Staples_return_order_no]))
),
NOT(ISBLANK('Query1'[return_status_description]))
),
NOT(ISBLANK('Query1'[customer_name]))
),
NOT(ISBLANK('Query1'[External_Document_No]))
),
NOT(ISBLANK('Query1'[Our_Item_No]))

),
NOT(ISBLANK('Query1'[EXTERNAL_ITEM_NO]))
),
NOT(ISBLANK('Query1'[Return_Node]))
),
NOT(ISBLANK('Query1'[Create_date]))
),
NOT(ISBLANK('Query1'[return_create_date]))
),
NOT(ISBLANK('Query1'[return_date_plus18days]))
),
NOT(ISBLANK('Query1'[Open_days]))
),
NOT(ISBLANK('LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Year]))
),
NOT(ISBLANK('LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Quarter]))
),
NOT(ISBLANK('LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[QuarterNo]))
),
NOT(ISBLANK('LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Month]))
),
NOT(ISBLANK('LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[MonthNo]))
),
NOT(ISBLANK('LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Day]))
)
)
),
"'Query1'[Invoice_Complete]", 'Query1'[Invoice_Complete],
"'Query1'[Invoiced_Quantity]", 'Query1'[Invoiced_Quantity],
"'Query1'[Staples_order_no]", 'Query1'[Staples_order_no],
"'Query1'[Staples_return_order_no]", 'Query1'[Staples_return_order_no],
"'Query1'[return_status_description]", 'Query1'[return_status_description],
"'Query1'[customer_name]", 'Query1'[customer_name],
"'Query1'[External_Document_No]", 'Query1'[External_Document_No],
"'Query1'[Our_Item_No]", 'Query1'[Our_Item_No],
"'Query1'[EXTERNAL_ITEM_NO]", 'Query1'[EXTERNAL_ITEM_NO],
"'Query1'[Return_Node]", 'Query1'[Return_Node],
"'Query1'[Create_date]", 'Query1'[Create_date],
"'Query1'[return_create_date]", 'Query1'[return_create_date],
"'Query1'[return_date_plus18days]", 'Query1'[return_date_plus18days],
"'Query1'[Open_days]", 'Query1'[Open_days],
"'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Year]", 'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Year],
"'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Quarter]", 'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Quarter],
"'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[QuarterNo]", 'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[QuarterNo],
"'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Month]", 'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Month],
"'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[MonthNo]", 'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[MonthNo],
"'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Day]", 'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Day]
)

VAR __DS0PrimaryWindowed =
TOPN(
501,
__DS0Core,
'Query1'[External_Document_No],
1,
'Query1'[Invoice_Complete],
1,
'Query1'[Invoiced_Quantity],
1,
'Query1'[Staples_order_no],
1,
'Query1'[Staples_return_order_no],
1,
'Query1'[return_status_description],
1,
'Query1'[customer_name],
1,
'Query1'[Our_Item_No],
1,
'Query1'[EXTERNAL_ITEM_NO],
1,
'Query1'[Return_Node],
1,
'Query1'[Create_date],
1,
'Query1'[return_create_date],
1,
'Query1'[return_date_plus18days],
1,
'Query1'[Open_days],
1,
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Year],
1,
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[QuarterNo],
1,
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Quarter],
1,
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[MonthNo],
1,
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Month],
1,
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Day],
1
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
'Query1'[External_Document_No],
'Query1'[Invoice_Complete],
'Query1'[Invoiced_Quantity],
'Query1'[Staples_order_no],
'Query1'[Staples_return_order_no],
'Query1'[return_status_description],
'Query1'[customer_name],
'Query1'[Our_Item_No],
'Query1'[EXTERNAL_ITEM_NO],
'Query1'[Return_Node],
'Query1'[Create_date],
'Query1'[return_create_date],
'Query1'[return_date_plus18days],
'Query1'[Open_days],
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Year],
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[QuarterNo],
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Quarter],
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[MonthNo],
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Month],
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Day]

d_gosbell

Super User

5 years ago

You have deleted much more than just the TOPN function making the query invalid. I've highlighted in red below what should be deleted in order to remove just the TOPN restriction.

VAR __DS0PrimaryWindowed =
TOPN(
501,
__DS0Core

,
'Query1'[External_Document_No],
1,
'Query1'[Invoice_Complete],
1,
'Query1'[Invoiced_Quantity],
1,
'Query1'[Staples_order_no],
1,
'Query1'[Staples_return_order_no],
1,
'Query1'[return_status_description],
1,
'Query1'[customer_name],
1,
'Query1'[Our_Item_No],
1,
'Query1'[EXTERNAL_ITEM_NO],
1,
'Query1'[Return_Node],
1,
'Query1'[Create_date],
1,
'Query1'[return_create_date],
1,
'Query1'[return_date_plus18days],
1,
'Query1'[Open_days],
1,
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Year],
1,
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[QuarterNo],
1,
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Quarter],
1,
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[MonthNo],
1,
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Month],
1,
'LocalDateTable_fd4ab7aa-4e0a-4c2f-baeb-1a0197162a03'[Day],
1
)

EVALUATE
__DS0PrimaryWindowed

Anonymous
Not applicable
5 years ago
Thankyou d_gosbell!!!It works
ugoriuko
Frequent Visitor
5 years ago
Hi d_gospel,

The below Query runs on DAX Studio, But returns no rows found in Report Builder.
I am passing the report Parameters @Location and @Date as the query parameters
What am I Possibly not doing?

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({@Location}, 'CSLOCATION'[Location])
VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('Date Dim'[Date])), 'Date Dim'[YYYY/MM]= @Date)
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
'CSPDETAILS'[Criteria],
__DS0FilterTable,
__DS0FilterTable2,
"Score", 'RM'[Score],
"Result", IGNORE('RM'[Result]),
"Color", IGNORE('RM'[Color])
)
VAR __DS0PrimaryWindowed =
TOPN(1001, __DS0Core, [Score], 0, 'CSPDETAILS'[Criteria], 1)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[Score] DESC, 'CSPDETAILS'[Criteria]
- d_gosbell
  Super User
  5 years ago
  ugoriuko wrote:
  
  The below Query runs on DAX Studio, But returns no rows found in Report Builder.
  
  I am passing the report Parameters @Location and @Date as the query parameters
  
  What am I Possibly not doing?
  
  I can't see how it could run in DAX Studio since your expression for __DS0FilterTable2 is not valid. It includes an extra unmatched closing parathesis and you can't filter using the [YYYY/MM] column when the table you are filtering only contains the values from the [Date] column.
  
  If you can run an All Queries trace against your data source you should be able to capture the full query including the parameter XML block when you run from Report Builder, then you should be able to see what values Report Builder is passing through. Maybe there is some extra formatting happening somewhere and the parameters values are being passed differently to what you expected.
  - ugoriuko
    Frequent Visitor
    5 years ago
    Thanks for the feedback. I actually have that properly setup as
    // DAX Query
    DEFINE
    VAR __DS0FilterTable =
    TREATAS({@Location}, 'CSLOCATION'[Location])
    VAR __DS0FilterTable2 =
    FILTER(
    KEEPFILTERS(VALUES('Date Dim'[YYYY/MM])), 'Date Dim'[YYYY/MM]= @Date)
    )
    VAR __DS0Core =
    SUMMARIZECOLUMNS(
    'CSPDETAILS'[Criteria],
    __DS0FilterTable,
    __DS0FilterTable2,
    "Score", 'RM'[Score],
    "Result", IGNORE('RM'[Result]),
    "Color", IGNORE('RM'[Color])
    )
    VAR __DS0PrimaryWindowed =
    TOPN(1001, __DS0Core, [Score], 0, 'CSPDETAILS'[Criteria], 1)
    EVALUATE
    __DS0PrimaryWindowed
    ORDER BY
    [Score] DESC, 'CSPDETAILS'[Criteria]
    
    I must have pasted something else from my clipboard.
    
    What do you mean by extra formatting?
    I am new to report builder and have no idea what is going on.

Forum Discussion

Parameters in Report Builder (DAX query copied from Performance Analyzer)

Related content

Reset Performance analyzer- For consistent duration

Performance Analyzer on Power BI Service

Matrix performance - w/ field parameters takes 5x longer

Performance analyzer

Performance Analyzer Doubt