THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Use parameters in your #DAX queries

The DAX query syntax allows you to insert a parameter by using the @ symbol.



    'Product Subcategory',

    'Product Category'[Product Category Name] = @Category )

Depending on the client and on the driver you are using to execute your DAX query, this syntax may be supported or not. Here is a short recap:

  • XMLA Execute Command: supported
  • OLE DB MSOLAP driver with ADO connection: supported
  • OLE DB MSOLAP driver with ADO.NET connection: not supported
  • OLE DB for OLAP MSOLAP driver with ADOMD library: supported
  • OLE DB for OLAP MSOLAP driver with ADOMD.NET library: supported
  • Reporting Services with Analysis Services driver: supported using DMX editor - not supported in Report Builder
  • Reporting Services with OLE DB driver: not supported (but a workaround is available)

Here is an example of an XMLA command that executes the initial DAX query passing Bikes as Category parameter.

<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">





            'Product Subcategory',

            'Product Category'[Product Category Name] = @Category )





            <Catalog>AdventureWorks Tabular Model SQL 2012</Catalog>










Passing the parameter to the OLE DB MSOLAP driver doesn’t work in .NET because of a problem in System.Data.OleDb assembly, as described in a Connect bug that has never been fixed despite the “Fixed” state of the issue (please vote it!). As a workaround, you can use the ADOMD.NET library in .NET and you can find a working example written in C# in this article on SQLBI web site.

Finally, you can use DAX queries in Reporting Services by using the DMX editor in BIDS/SSDT/Visual Studio (you know, there are a lot of names for the same thing!). In fact, standard user interface for MDX only supports MDX queries, but if you select the "Command Type DMX" button (which is used to insert data mining queries) then you will be able to insert a DAX query with a parameter (remember to manually insert the parameters without the @ prefix by using the Query Parameters button).


UPDATE: A Step-by-Step guide describing how to write queries in Report Builder is available in a new blog post.

However, you cannot use DAX queries at all in Report Builder by using the standard Microsoft Analysis Services Data Source with Report Builder, because the user interface doesn't support DMX and only supports MDX queries. Instead, you can use the standard OLE DB connection type, by passing MSOLAP as a provider name.

Define OLE DB Data Source using MSOLAP in Reporting Services

UPDATE: A step-by-step guide describing how to write queries in Report Builder is available in a new blog post.

However, even in this case parameters in DAX queries are not supported, probably because of the same bug on System.Data.OleDb. Maybe that when this bug will be fixed, also Report Builder (and Reporting Services deisgner in Visual Studio) will work. In the meantime, you have to rely on string concatenation in order to pass a parameter to a query. In other words, you should set the Query.CommandText properties with the following expression:   

'Product Subcategory',
'Product Category'[Product Category Name] = """ & Parameters!Category.Value & """ )"

I hope that the OLE DB support from ADO.NET will be fixed by Microsoft. I understand that OLE DB has been deprecated, but I don’t understand why we should wait a months if not years in order to get an ODBC driver for Analysis Services instead of a quick fix for that. Reporting Services developer using DAX would really appreciate this gift!

UPDATE: I fixed the post on January 5, 2012 thanks to Greg Galloway that pointed me to DMX Builder in Visual Studio

Published Thursday, January 5, 2012 4:47 PM by Marco Russo (SQLBI)



Omar Sultan said:

Hello Marco,

I was wondering if you have done anything similar however using Hierarchies?  I'm trying to create a tree filter that will work on the Date Hierarchy without having to break it down into three separate filters (Year - Quarter - Month)

June 29, 2013 6:07 AM

Marco Russo (SQLBI) said:

In DAX a hierarchy is just a set of 3 columns and you cannot apply a Direct parameter - probably you should rely on MDX in that case, or writing a complex IF statement in Reporting Services in order to build the proper DAX string.

July 7, 2013 5:00 PM

Ed said:

Hi Marco,

Is there a reason why using the @ symbol for dynamic columns would not work?

For example:

'Product Category'[@CategoryName]

I have a query that works properly using string concatenation and dynamic columns, but it does not work with the @ symbol. I also need multi-valued parameter filtering in the same query and following Chris Webb's post on that uses the @ symbol, which restricts me from using string concatenation.



July 18, 2013 2:02 PM

Marco Russo (SQLBI) said:

It's semantic problem, it would not work in SQL too and strong concatenation is the only way.

July 18, 2013 2:13 PM

SQLBI - Marco Russo said:

If you use Report Builder with Reporting Services, you can use DAX queries even if the editor for Analysis

May 21, 2014 7:40 AM

Andrew said:

Hi Marco,

Is it possible to execute XMLA command in DAX Studio? It would be nice to use this to add parameters to some DAX queries that I wrote for data validation purposes.

October 22, 2014 7:54 PM

Marco Russo (SQLBI) said:

In the upcoming release there will be a Direct support for merging parameters!

October 23, 2014 12:48 AM

Andrew said:

Awesome! I'll look forward to that, thank you for getting back to me.

October 23, 2014 2:20 PM

Andrew said:

Good day sir-

Just downloaded the new release of DAX studio and am looking forward to beginning to use parameters in my DAX queries. Great work!

December 18, 2014 1:35 PM

Marco Russo (SQLBI) said:

Thanks for the feedback!

December 18, 2014 1:39 PM

Mike said:

Can you provide an example of how parameters can be used with DAX Studio?

April 15, 2015 9:23 PM

Marco Russo (SQLBI) said:

Mike, you can copy the XML code block from SQL Profiler containing the parameters and DAX Studio merge them with the code.

May 9, 2015 6:18 AM

Tycho said:

Hi, this may be partly off-topic, but I was actually interested in the list of ways to run DAX queries you provided.

I'm not really very knowledgeable on all these connectors, but I recall that Pentaho's Mondrian also used this XMLA to provide an MDX interface on regular SQL DBs.

I'm wondering if it might be possible to similarly run DAX queries on non-Microsoft data stores/engines that can use SQL, similar to how SQL Server's DirectQuery mode could convert DAX queries to SQL.

I'd be particularly interested in executing DAX queries (or better, MDX + DAX, as in Excel pivots) on Apache Spark here, as it would allow distributing bigger calculations over a cluster, which isn't an option with the MS stack. So on the one hand, it'd enable scaling up to bigger DAX queries, on the other, opening up DAX to a much wider potential audience.

I found Spark had an ODBC/JDBC driver from Simba Technologies (, though I suppose ODBC isn't OLE DB. So I'm guessing Spark definitely wouldn't be an option as it stands.

Might other databases though (assuming they can be used with OLE DB)? Or would you have pointers as to how much would need to be done to make this possible? I'd appreciate any insight. :)

July 6, 2015 9:27 PM

Marco Russo (SQLBI) said:

DirectQuery currently supports only SQL Server. It is not possible to use a generic OLEDB / ODBC driver. At the moment, there is no support for other databases. Maybe this will change in the future, but I don't have any info about that.

July 7, 2015 2:56 AM

Mr Ty said:

I'm using a DAX query to query the fact table in my tabular model for data for a SSRS report.

I've used the calculatetable function to apply filters and got all of the fact table columns into my "Fields" in my report datatset. The problem is I need some other fields from connected dimension tables on my report. Is there a way to pull columns from multiple tables using the calculatetable table function? Or (kind of what the name suggests) can the calculatetable function reference two tables at the same time?

Hope my question makes sense.

February 24, 2016 4:11 PM

Marco Russo (SQLBI) said:


You might find useful info in these articles if you know SQL:

February 24, 2016 7:02 PM

Raman said:

Hi Marco,

If I need to create a SSRS report using a Tabular Model as source, which language (MDX or DAX) should I prefer to write SSRS query?

Which language will be more flexible to create parameters and complex calculation?

March 13, 2016 10:21 PM

Marco Russo (SQLBI) said:

DAX is more flexible and the suggested way for SSRS.

March 23, 2016 2:33 AM

ela shaked said:

Hi Marco,

I think, I will write "thank you" in comments of all of you topics. ;-)

Thank you.

A string like this :


......... & Parameters!Category.Value & """ )""

I put it  in a "Parameter_Query" parameter and get dataset with

flexible filter for one or many products.

Could i realize it any other way ?

November 24, 2016 8:26 AM

pevans360 said:


Needed a parameter of type int.  Apparently the default is text/string because the query returned an error "does not support comparing values of type Int with type text".  

Using a parameter of type int required adding xml namespaces to the Parameters tag and an xsi:type to the Value tag as below:

<Parameters xmlns:xsi="" xmlns:xsd="" xmlns="urn:schemas-microsoft-com:xml-analysis">



<Value xsi:type="xsd:int">18</Value>



November 25, 2016 3:18 PM

pevans360 said:

In SSMS, an issue with the XMLA approach is that the results are returned as XML text.  There's no way to get a grid.  

In this case, in recent releases, can use a VAR in an MDX query window to serve the same purpose:

define var areaID = 53


filter(areas, areas[ID] = areaID)

Results are returned in the standard grid.  

November 26, 2016 12:30 AM

Marco Russo (SQLBI) said:

@ELA: yes, and it is much easier using the new IN operator (when it will be available in SSAS Tabular)

#pevans360: I don't understand exactly whether your workaround is just for using SSMS or it has a broader scope. Can you clarify? Thanks!

January 19, 2017 2:28 PM

Krishna said:

Hi Mark,

Is there any function or way for passing multiple parameter from SSRS to DAX query except PathContains function, It's a time consuming when I set 2-3 pathcontains function in 2-3 variable for accepting multiple value coming from Reporting side.

Thanks in Advance.

February 11, 2017 6:35 AM

Marco Russo (SQLBI) said:

You can use the new IN syntax in DAX (but you need SSAS Tabular 2016). You can find more details here:

February 15, 2017 11:58 AM

Rick said:

I am struggling with cascading DAX parameters in SSRS. In short second parameter values not filtered by first parameter. If this is not the correct Blog spot my apologies. Any examples or direction would be really appreciated.

May 30, 2017 10:15 AM

Marco Russo (SQLBI) said:

@Rick: you should look in SSRS documentation or blog post. The idea is that you have to reference the first SSRS parameter in the query used to populate the list of the second parameter

June 5, 2017 11:17 AM

Vitaly Popov said:

Hello, Marco.

I need to create a report where I could specify in the parameter a hierarchy for output. As example - parameter Hierarchy, values list: Category, SubCategory, Product. Result - table of sales by selected hierarchy (Columns in Data Model: 'Product Category'[Category] etc). In MDX it is easy - StrToMember, but how to do it in DAX? Any ideas? Thanks!

July 4, 2018 2:28 AM

Marco Russo (SQLBI) said:

Unfortunately there is no "STRTOMEMBER" equivalent in DAX.

July 6, 2018 10:07 AM

Vitaly Popov said:

Thank you, Marco.

I solved it.

July 11, 2018 1:06 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement