Ways to enter DAX code

[Mightystomp]

Hi

I am trying to teach myself DAX. My question is this.

At the moment entering all code through "Calculated fields" in the Power Pivot tab in Excel (Excel 2013). Is there any other way of entering code? I ask because i find the small window in "Calculated Fields" restricting when writing longer code. Either i need to condense (not use line shifts) the code making it less legiable or not being able to see the whole code at a time.

Thnak you

 

[Matt Allington]

So many things to cover.

Teaching yourself DAX can be a challenge. I have taught lots of "self taught" DAX students in classes, and they alway say "I have to go back and start again". Of course maybe the way you are learning is different, but just be aware that it is hard to teach yourself. If you want a cheap, curated approach, then consider my book. Learn to Write DAX - Excelerator BI

In my view, writing your code in the Calc Field dialog is the best approach. First set up a pivot table, then click in the pivot table, then add the formula as you describe. Once you are done, the formula appears in the pivot table and you get immediate feedback if the formula is correct. There is another way. Go to the Power Pivot window (back end), go to the data view and then the calculation pane at the bottom. You can write measures there. But I don't think it is as good - but it is up to you.

You can make the calc field dialog larger by expanding the dialog box. You can also increase the font size by using Ctrl + Mouse Wheel. Use Shift + Enter to create a new line and lots of spaces to indent. You can also cut and paste your formulas to DAX Formatter by SQLBI for syntax checking and formatting

 

[Mightystomp]

I have actually been reading some of your posts on PowerPivotPro. Amongst these are 5-common-mistakes-made-by-self-taught-dax-students. I am happy to say that in my current project I have not used any calculated columns. I almost gave up and went that way but found a solution online. I do not however understand the solution which leaves me unhappy.

I did then order your book to better understand the basics and syntax. I have background as a Poweruser of Excel and VBA programming(macros).

As you say measures are quite easy to test as you get the results in the pivottable. What I find difficult at the moment is working with measures which uses calculated tables (like rankx) as theese are calculated internally and the results are not always helpful in understanding what is happening. Is there anywhere I can write DAX queries and see the resulting tables? Even better, any way of this in the pivottable with row and filter context?

Thank you for your tips about the DAX Formatter by SQLBI. It will help a great deal.

 

[akice]

Is there anywhere I can write DAX queries and see the resulting tables?

Check out the companion to Dax Formatter: Dax Studio It takes some studying to get it to work, but can be invaluable in figuring out what is going on in a measure.

 

[Matt Allington]

As Akice said, DAX studio is good. I wrote about it here along with tips on how to materialize tables Getting Started with DAX Studio - Excelerator BI

also look at Power BI desktop. It has a New Table button that allows you to materialise a table for testing purposes - among other things I'm sure.