DAX Query Basics

  • Thanks for the great article.

    I suppose for SQL people it might look unreadable but I found your examples very easy to understand. There's a clear nested structure

    Evaluate<-Rows<-Row functions etc

    I agree that for someone from an MDX perspective this is a godsend, it is certainly looking a lot easier to write.

    DAX is not just for powerpivot, it's a core part of SSAS tabular model which I am sure will overthrow multidimensional cubes once it is mature. It would certainly take off if competitor products start using DAX as they did with MDX (e.g. IBM, Petaho and other OLAP cube vendors)

    Looking forward to follow up articles

  • I agree with loren.saunders: DAX seems to have a lot of similarities with LISP (a.k.a. "Lot of Idiot and Stupid Parentheses", at least for me, an 'aficionado' of Prolog and APL).

    My question is another one: why I need it ?

    What are the characteristics that make DAX a language to learn absolutely ?

    Please don't tell me "the speed": even with the standard "relational" there are queries (among the ones posted) that are faster in SQL than in DAX (the ones where xVelocity has to decompress/rebuild many columns for many rows), and with SQL2104 the "in memory" engine promises to be as fast (even if it has to decompress/rebuild records).

    I'm looking for differences in expressiveness. The same that allows me to say that there are task 'natural' in Prolog and difficult/impossible in 'C' (the reverse is true, obviously).

    Without that knowledge, DAX remains for me a JAL (Just Another Language).

    No, please. I'll stay with MDX (even if somebody says that "it's a wall").

  • I agree with loren.saunders: DAX seems to have a lot of similarities with LISP (a.k.a. "Lot of Idiot and Stupid Parentheses", at least for me, an 'aficionado' of Prolog and APL).

    My question is another one: why I need it ?

    What are the characteristics that make DAX a language to learn absolutely ?

    Please don't tell me "the speed": even with the standard "relational" there are queries (among the ones posted) that are faster in SQL than in DAX (the ones where xVelocity has to decompress/rebuild many columns for many rows), and with SQL2104 the "in memory" engine promises to be as fast (even if it has to decompress/rebuild records).

    I'm looking for differences in expressiveness. The same that allows me to say that there are task 'natural' in Prolog and difficult/impossible in 'C' (the reverse is true, obviously).

    Without that knowledge, DAX remains for me a JAL (Just Another Language).

    No, please. I'll stay with MDX (even if somebody says that "it's a wall").

  • Gary -- I've finally gotten around to teaching myself PowerPivot and I'm excited by the possibilities. Knowing DAX appears to be critical for maximizing PowerPivot's potential. Having DAX and SQL equivalent references side-by-side is extremely useful. Thanks for taking the time to create this!

    Eric

  • This bit of code needs to be corrected 😉

    evaluate(

    ROW ( "First Year" , Year(Date(1900,1,1)),

    "First Month", Year Month,(Date(1900,1,1)),

    "First Day", Day(Date(1900,1,1)) )

    )

    Great post that reminds me I need to do more with power pivot.

  • Awesome article. Can't wait for the next in the series!

  • Excellent work, Gary!

    Many thanks.

  • Thanks for all your feedback (both positive and negative). I've made a correction to the code sample that Christian and others highlighted (thanks).

    Regarding the question around the motivation to learn DAX I would say that you should embark on any endeavour that you find satisfaction in.:-)

    I enjoyed learning DAX and I'm now enjoying developing products with it (outside power pivot). I created my article simply to help others with that learning curve. If you take out all the additional commentary the article is simply a cheat sheet for SQL developers taking a test drive in SSAS Tabular.

  • I don't write code to get any satisfaction, I write code to solve problems.

    And it is absolutely not clear to me what is the class of problems on which, using DAX, I have some advantage in comparison with other languages (SQL or MDX or Prolog or APL2).

    E.g. at an Italian conference Alberto Ferrari spent some time to show how he could do ABC analysis using DAX, or even how much DAX was appropriate to solve this class of problems. I contested that statement and, at home, in half an hour, I was able to solve the same problem with MDX.

    So, again: why should I learn DAX ? not only to have fun, please ...

  • If Alberto Ferrari or Marco Russo were unable to convince you of the languages merits then I very much doubt I can. I feel sorry that you get no satisfaction in your work and you're only programmed to solve problems. 😉

    In a nut shell: Want the power of the veripaq engine? then use DAX.

    Although SSAS 2012 Tabular will except MDX queries, this is more for retro-fitting into legacy products and doesn't get the most out of the vertipaq engine.

  • Strange ! I was convinced that my work were "solve problems". The code is a mean (among others) to do that.

    If you are happy to write useless code, than it's your problem ...

  • dear teodorom, what is the point of your responses as they do NOTHING help? You appear to be a professional so please act like one.

  • So, telling me that I'm "programmed" is professional ?

  • @teodorom

    Dude I've been adding in smiley faces with my comments to indicate that I'm being friendly and my comments are in jest. People visit this site to get informed, contribute to the community and openly share ideas/experiences.

    I've taken time out to write this article and respond to readers responses. Please appreciate that 🙂

  • Happy new year to everyone.

    I have taken some time to look at language trends using google's online trending app. If you view the trending graph in the link, you'll see that there is a steady interest the MDX language and a small but growing interest in tabular/DAX.

    So in response to concerns about whether it is a worthwhile language to learn. You could say from a demand/trending point of view it's to early to predict.

    So you could compare it to a fledgling stock; get into it early and, in time, it can yield great returns or alternatively end in tears. 🙂

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply