Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
jgray72
Helper II
Helper II

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

‎01-27-2020 08:05 AM

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 from my Power BI report.  The difficulty that I have is building  parameterd off the copied query.  I can't figure out how to edit the query to allow for a parameter on "Season" or "Brand".  Where/how would I make those edits in my query?  

 

 

Labels:
Message 1 of 17
9,955 Views
0
16 REPLIES 16
dsandberg
Helper II
Helper II

‎04-15-2021 09:53 AM

Hi folks... I was able to get parameters to work a variable like:

VAR __DS0FilterTable5 = 
    TREATAS({@paramCropYear}, 'Planted Crop'[CropYear])

 

 

DEFINE
 VAR__DS0FilterTable5 = 
    TREATAS({@paramCropYear}, 'Planted Crop'[CropYear])

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP(
          'Planted Crop'[CropYear],
          'Planted Crop'[Farm],
          'Planted Crop'[Variety],
          'Planted Crop'[Field]
        ), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable5,
      "Target_Seed_Spacing", 'Planter Evaluation'[Target Seed Spacing],
      "Projected_Spacing", 'Planter Evaluation'[Projected Spacing],
      "Spacing____", 'Planter Evaluation'[Spacing +/-],
      "Target_Plant_Population", 'Planter Evaluation'[Target Plant Population],
      "Projected_Plant_Population", 'Planter Evaluation'[Projected Plant Population],
      "Plant_Population_Observed_vs__Target", 'Planter Evaluation'[Plant Population Observed vs. Target],
      "Ideal_Seed_Placement__", 'Planter Evaluation'[Ideal Seed Placement %],
      "Double_Seed_Placement__", 'Planter Evaluation'[Double Seed Placement %],
      "Skip_Seed_Placement__", 'Planter Evaluation'[Skip Seed Placement %],
      "Exact_Seed_Placement__", 'Planter Evaluation'[Exact Seed Placement %]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0Core,
      [IsGrandTotalRowTotal],
      0,
      'Planted Crop'[CropYear],
      1,
      'Planted Crop'[Farm],
      1,
      'Planted Crop'[Variety],
      1,
      'Planted Crop'[Field],
      1
    )

EVALUATE
	FILTER(__DS0PrimaryWindowed, 'Planted Crop'[CropYear] >2000)

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'Planted Crop'[CropYear],
  'Planted Crop'[Farm],
  'Planted Crop'[Variety],
  'Planted Crop'[Field]

 

Message 17 of 17
7,392 Views
Anonymous
Not applicable

‎02-03-2021 08:41 AM

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]

Message 10 of 17
8,213 Views
0
d_gosbell
Super User
Super User
In response to Anonymous

‎02-03-2021 01:40 PM

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. 

 

 

// 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]

Message 11 of 17
8,175 Views
ugoriuko
Frequent Visitor
In response to d_gosbell

‎08-23-2021 10:11 PM

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]

 

Message 13 of 17
5,968 Views
0
d_gosbell
Super User
Super User
In response to ugoriuko

‎08-23-2021 10:59 PM
@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.

Message 14 of 17
5,950 Views
0
ugoriuko
Frequent Visitor
In response to d_gosbell

‎08-26-2021 09:39 PM

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.

Message 15 of 17
5,767 Views
0
d_gosbell
Super User
Super User
In response to ugoriuko

‎08-26-2021 10:07 PM
@ugoriuko wrote:

What do you mean by extra formatting?

So if your @date parameter is setup as a datetime data type you might find it is actually sending through the data in a format something like "dd/mm/yyyy" instead of "yyyy/mm". If you use the All Queries trace in DAX Studio or if you use SQL Profiler and capture the BeginQuery event you will be able to see the values the parameters is passing through.

 

The other trick I've used sometimes is to temporarily put text boxes on my report linked to the parameters so that I can see the parameter values being passed through that way.

Message 16 of 17
5,750 Views
0
Anonymous
Not applicable
In response to d_gosbell

‎02-03-2021 03:53 PM

Thankyou d_gosbell!!!It works 

Message 12 of 17
8,152 Views
bradsy
Microsoft Employee
Microsoft Employee

‎01-28-2020 04:53 PM

Experiment with the DAX query editor by adding removing parameters. This looks like a reasonable article: https://www.mssqltips.com/sqlservertip/5865/sql-server-reporting-services-report-builder-with-dax-qu...

Message 2 of 17
9,908 Views
Daviejoe
Memorable Member
Memorable Member
In response to bradsy

‎09-11-2020 07:09 AM

Great article!  This will be a big help!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 9 of 17
9,207 Views
0
jgray72
Helper II
Helper II
In response to bradsy

‎01-29-2020 08:50 AM

Hi - I had found that article as well, but I can't nail down the syntax to add the parameters in the DAX query.  This is what I just tried and it's incorrect.

 

  paramter dax.PNG

JG

Message 3 of 17
9,883 Views
0
jgray72
Helper II
Helper II
In response to jgray72

‎01-29-2020 08:58 AM

I've also tried playing around with the @, but it won't work either.

 

dax query 2.PNG

 

JG

Message 4 of 17
9,877 Views
0
d_gosbell
Super User
Super User
In response to jgray72

‎09-15-2020 11:18 PM

So one way to do this is to add a filter to your visual in Power BI for the Brand and Season. This will add an "_DS0FilterXXX" variable to your query and inject it into the appropriate place in the SUMMARIZECOLUMNS expression.

 

So if you set a Brand filter of "XYZ" and a season filter of "Winter" the you would just need to find these two strings in the query you copy from the Performance Analyzer and then replace the hard coded values with a reference to @Brand and @Season (or you could use the Parameters!Brand.Value string expression approach)

Message 5 of 17
9,093 Views
0
Jdokken
Helper III
Helper III
In response to d_gosbell

‎08-03-2021 12:26 PM

When I try changing the "APR-21" to @YearMonth, I get a message saying the parameter is not declared. 

   VAR __DS0FilterTable3 = TREATAS({"APR-21"}, 'Period'[GL Period])

Jdokken_0-1628018657737.png

 

Message 6 of 17
6,383 Views
0
blynchdata
Resolver I
Resolver I
In response to Jdokken

‎08-04-2021 10:30 AM

When I receive this error, I have to add the query parameters here in the Query Designer, then that error about it being declared goes away.

blynchdata_0-1628098068516.png

 

Message 7 of 17
6,334 Views
0
Jdokken
Helper III
Helper III
In response to blynchdata

‎08-04-2021 02:36 PM

How did you write it into the query?  As a Variable like this?

VAR __DS0FilterTable3 =
TREATAS({"@YearMonth"}, 'Period'[GL Period])

 

Here's my full DAX Query from Power BI's Performance Analyzer. 

// DAX Query
DEFINE
MEASURE 'Internal Financial Metrics'[-Mgn R&O Filter] =
(/* USER DAX BEGIN */
IF(
([CY R&O] = 0 || [CY R&O]= BLANK()) &&
([NY R&O] = 0 || [NY R&O]= BLANK()) &&
([FY R&O] = 0 || [FY R&O]= BLANK()),
"Don't Include", "Include")


/* USER DAX END */)

VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Internal Financial Attributes'[Project Status - Reporting])),
NOT('Internal Financial Attributes'[Project Status - Reporting] = "IATP")
)

VAR __DS0FilterTable2 =
TREATAS({"Y"}, 'Reporting Filters'[Filter for Teaming View])

VAR __DS0FilterTable3 =
TREATAS({"MAY-21"}, 'Period'[GL Period])

VAR __DS0FilterTable4 =
TREATAS({"Thousands"}, 'Reporting Multiples'[Reporting Units])


VAR __ValueFilterDM1 =
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'Internal Financial Attributes'[Group Name],
'Internal Financial Attributes'[GROUPSORTORDER],
'Project'[Project Name],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
"ATD___Complete", 'Internal Financial Metrics'[ATD % Complete],
"CY_R_O", 'Internal Financial Metrics'[CY R&O],
"NY_R_O", 'Internal Financial Metrics'[NY R&O],
"FY_R_O", 'Internal Financial Metrics'[FY R&O],
"Total_R_O", 'Internal Financial Metrics'[Total R&O],
"Cash_Position", 'Internal Financial Metrics'[Cash Position],
"R_O___Group_Margin_Fcst", 'Internal Financial Metrics'[R&O + Group Margin Fcst],
"MinRisk___Opportunities_Comments", CALCULATE(MIN('Internal Financial Attributes'[Risk & Opportunities Comments])),
"-Mgn R&O Filter", IGNORE('Internal Financial Metrics'[-Mgn R&O Filter])
)
),
[-Mgn R&O Filter] = "Include"
)


EVALUATE
__ValueFilterDM1

 

Jdokken_0-1628113343349.png

 

Message 8 of 17
6,317 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All