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 !