THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

How to pass a #DAX query to DAX Formatter

In its first two months, DAX Formatter served 3,500 requests and I see the daily trend slowly raising. If you have observed carefully the first articles published on DAX Patterns, you might have seen that you can click the link”Code beautified with DAX Formatter”.

image

When you click that link, you open the DAX Formatter page copying the query/formula shown in the box. The good news is that you can implement the same behavior in your articles/blogs/posts by using a GET or POST call.

The easiest way is passing the query into the URL of a GET command:

http://www.daxformatter.com/?fx=FORMULA&r=REGION

The &fx argument is the dax code you want to format. The &r argument is optional and can be US (the default), UK or Other. Using Other you use the semicolon ( ; ) as a list separator, and comma ( , ) as decimal point, instead of the , and . settings used for US and UK. Here are two examples of the same query formatted with the two settings.

http://www.daxformatter.com/?fx=EVALUATE%20calculatetable(Customers,Customer[Occupation]="Student")&r=US

http://www.daxformatter.com/?fx=EVALUATE%20calculatetable(Customers;Customer[Occupation]="Student")&r=EU

Using the URL might have different limits for its length, depending on the browser. We can consider 2000 characters as a practical limit. You can overcome this limitation by using a POST command. Here is an example of a simple html form that pass the content of a textbox as the query to format:

<form action="http://www.daxformatter.com" method="post">
        <input type="hidden" name="r" value="US" />
        <textarea name="fx">EVALUATE calculatetable(Customers,Customer[Occupation]="Student")</textarea>
        <input type="submit" />
</form>

I have also received many feedback about many possible improvements of DAX Formatter – we’ll work on it, you just have to wait… but thanks for the support and appreciation!

UPDATE Feb 27, 2014 

You can now use the URL syntax with the additional arguments:

embed=1 : request only the HTML formatted code

font=n : optional - set the font size

For example:  

http://www.daxformatter.com/?embed=1&fx=EVALUATE%20calculatetable(Customers,Customer[Occupation]="Student")&r=US 

http://www.daxformatter.com/?embed=1&font=22&fx=EVALUATE%20calculatetable(Customers,Customer[Occupation]="Student")&r=US

Published Monday, February 24, 2014 2:06 PM by Marco Russo (SQLBI)
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Chris Webb said:

Hey, last time I checked the UK and Ireland were still in the EU, and we use commas as list separators and full stops (.) as decimal separators!

February 24, 2014 7:32 AM
 

Marco Russo said:

Ops, my fault, and I don't want to be responsible for a diplomatic issue! :)

We'll fix this asap... stay tuned!

February 24, 2014 10:50 AM
 

ruve1k said:

Is there any way to just get back the HTML-formatted DAX instead of the entire webpage?

February 24, 2014 10:09 PM
 

Marco Russo (SQLBI) said:

You can obtain the HTML by using the COPY HTML button. Or do you want to automate the operation? May I ask in which scenario?

Marco

February 25, 2014 6:22 AM
 

Marco Russo (SQLBI) said:

Chris: we've just fixed - now UK can be used as country just as US :-) and we use Other for other settings. No political issues now!

February 25, 2014 7:53 AM
 

Bertrand said:

Thanks for this great tool. In order to document a whole workbook, I used the following query to extract all the measures (DAX Studio):

select MEASUREGROUP_NAME, MEASURE_NAME, [DESCRIPTION], '[' + MEASURE_NAME + ']:=' + EXPRESSION + ';' AS [DAX EXPRESSION]  from $SYSTEM.MDSCHEMA_MEASURES WHERE CUBE_NAME='Model' ORDER BY MEASUREGROUP_NAME

Then copied/pasted the whole [DAX EXPRESSION] column, and saved the formatted result as a docx document. Tadaaa !

February 25, 2014 8:07 AM
 

Marco Russo (SQLBI) said:

Bertrand,

this is a good idea - just be careful of additional measures created in MDX, for example for KPIs - you might filter them by using this query:

SELECT

 MEASUREGROUP_NAME, MEASURE_NAME, [DESCRIPTION],

 '[' + MEASURE_NAME + ']:=' + EXPRESSION + ';' AS [DAX EXPRESSION]

FROM $SYSTEM.MDSCHEMA_MEASURES

WHERE CUBE_NAME='Model'

 AND MEASURE_AGGREGATOR = 0

ORDER BY MEASUREGROUP_NAME

However, I would use the following two queries instead, one for the calculated columns and the other for the measures:

SELECT

 '''' + [TABLE] + '''[' + [OBJECT] + '] = ' + [EXPRESSION] + ';' AS [DAX EXPRESSION]

FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY

WHERE OBJECT_TYPE = 'CALC_COLUMN'

ORDER BY [TABLE] + '[' + [OBJECT]

SELECT

 '''' + [TABLE] + '''[' + [OBJECT] + '] := ' + [EXPRESSION] + ';' AS [DAX EXPRESSION]

FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY

WHERE OBJECT_TYPE = 'MEASURE'

ORDER BY [TABLE] + '[' + [OBJECT]

Let me know if they work for you!

Marco

February 25, 2014 8:33 AM
 

ruve1k said:

Marco,

My intention was to automate the operation. I have a VBA userform that I use to allow quick access to the command text of query tables. I thought it might be possible to set it up so that by clicking a button the DAX can be sent to your DAX Formatter service and then the returned HTML-formatted DAX can be displayed in a WebBrowser control.

February 25, 2014 1:41 PM
 

Marco Russo (SQLBI) said:

Good news - we implemented that - take a look at the updates in the blog post.

February 27, 2014 1:34 PM
 

Ola said:

December 6, 2014 4:32 AM

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement