With any language – code or real language – when you don’t use it too often, you’d tend to forget. Every now and then, it’s good to come back to the resources that you use when learning the programming language at hand. In this instance, I’d like to recommend that one of the best resources online to write DAX queries (ones that you write on SQL Server Management Studio) is Chris Webb’s (b | t). Below is his list of 6 part blog posts on DAX Queries and the main take away of the syntax used
- DAX Queries Part 1 – Evaluate, Filter and Order By
- DAX Queries Part 2 – Summarize, DistinctCount, Sum and Roll Up
- DAX Queries Part 3 – AddColumns
- DAX Queries Part 4 – CrossJoin and Values
- DAX Queries Part 5 – TopN, Generate and Define Measure
- DAX Queries Part 6 – Row
When you are writing DAX queries on SQL Server Management Studio (SSMS), my best advice is to save often. There is (almost) nothing worse like losing DAX or MDX queries that you have crafted for hours (or days). If you have installed SSMS Tools Pack which I have reviewed earlier, you might find an undocumented feature where it actually saves both DAX and MDX queries that you have run on Analysis Services instance. At the time of writing, I am using version 18.104.22.168 of SSMS Tools Pack.
To check queries that you have executed you simply go to SSMS Tools Pack > SQL History > Search Local History (Ctrl K + Ctrl H) on SSMS, just like you would do with SQL queries.
Then perform a search, say “EVALUATE” as shown on the screenshot below.
SSMS Tools Pack is generally SQL oriented, and I am very delighted to have discovered this undocumented feature. I have suggested to the creator, Mladen Prajdić (b | t) to include some capabilities for recovering unsaved DAX and MDX files. Hopefully we’ll see that in the upcoming release.
That’s all in this #InAJiffy blog post. Hope you find it light and useful.