PowerPivot DAX, i love it!

Vidas Matelis has been twittering live from the DAX session at the SQLPass. I just wanted to share these great new function available in DAX:

  • Data Analysis Expressions = DAX
  • DAX lets user do multidimensional analysis without user knowing that this is multidimensional analysis
  • sample DAX: =[Qty]*[Price] – syntax just like Excel.
  • DAX is not replacement for MDX
  • DAX provides functions that implement relations database concepts: Filter tables, aggregates, follow relationships
  • one of the many sample: =SUMX(RELATEDTABLE([Sales], Sales[Amount]) DAX
  • dax has functions to assist with dynamic aggregations of measures: dynamic sample: =VALUES(Time[year]) & “.” & VALUES(Product[ProductID])
  • DAX sample: =IF(VALUES(Time[Year])=2008,”Baseline”,”normal”)
  • More than 80 Excel functions in DAX
  • CTP3 has new FORMAT function to allow to convert any number to string
  • DAX sample: [salesAmt]/[SalesAmt](All(Product)) – use measure as function.
  • DAX = Sales[SalesAmt]/CALCULATE(Sales[SalesAmt], ALL(Product)) more complex syntax
  • CTP3 DAX has 35 Time Intelligence Functions – require date column in the data. Column type Date
  • DAX this version does not have custom time periods and weeks. Works with Yr, Qtr,Mth,Day
  • DAX: FirstDate, LastDate, FirstNonBlank, StartOfMonth, StartOfQtr, EndOfYear
  • DAX: DateAdd, DatesBetween, DatesInperiod – 26 function that return a table of dates
  • DAX: PreviousDay, Nextmonth, DatesMTD, DatesYTD, TotalMTD, OpeningBalanceQuarter, ClosingBalanceYear, etc <-more funct
  • Year over year growth: =Sales[SalesAmtt]-Sales[SalesAmth)(DateAdd(Time[Date],-1,Year].All(Time))
  • AllTime – need to add now in CTP3, not in RTM(will be under cover). So that first year has data.
  • QTD Sales =TotalQTD(Sales[SalesAmt],Time[Date], All(Time))
  • DAX yearAgo:=Sales[SalesAmt)(ParallelPeriod(Time[Date],-12, Month).All(Time))
  • autocomplete in CTP3 add single quotes, but they are optional.

Wow i can’t wait to start playing with these new  functions ! Thanks Vitas for sharing the info !

I also really really hope these time functions will be available in SSAS in the future, they won’t be availble in R2 Donald Farmer told me.

update: What will happen to the time functions when using a SSAS source? Is a Date dimension ok ? I hope so !