Thursday, May 16, 2013

DAX Literal Date Syntax changed with Compatibility Mode 11.0.3

Just a quick post here about a situation that came up with literal dates in DAX when a client downloaded one of my DAX for T-SQL folks scripts and found that it didn't work. The statement is this:


evaluate (

FILTER('Date','Date'[Full Date] = "2013-04-01")

)


You receive an error stating a data type mismatch and the above doesn't run.

The problem is with the compatibility mode your BISM Tabular model is in.  In 11.0.0 the above syntax works fine but with 11.0.3 you get the mismatch error.

The statement above needs to use the FORMAT function with 11.0.3 as below:


evaluate (

FILTER('Date', FORMAT('Date'[Full Date],"YYYY-MM-DD") = "2013-04-01")

)


Hope this helps and included the actual code below to download.

Monday, May 6, 2013

Measures, Non-Measures, or a Combo in Power View

If you are new to using Power View you may notice that there are images on the left of the available fields in the Field List:



When you are creating your visualizations it is important to know what numbers you can see and what they can be sliced by:  E.g. Product Profit by Sales Number.

Numbers are Measures represented by the Calculator. E.g. Product Cost & Product Profit above.

Slicers are Non-Measures represented by no image. E.g. Sales Category above.

Combo is represented by the greek letter Sigma. E.g. SalesNumber above.

So lets drag them into a blank Power View report to demonstrate the above.


Product Profit sliced by Sales Category and Count of SalesNumber.

When using the combo field SalesNumber I chose to a Distinct Count of SalesNumber to show SalesNumber as a Measure to answer the business question how many Sales were attained. This can be done in the Layout Section which is directly below the Fields List section in Power View.

.


To change the combo field SalesNumber from a Measure to a Non-Measure or Slicer, simply select the "Do Not Summarize" option and you will notice it change in Power View as below to answer how much profit per transaction was attained:



I will be explaining this in further detail and covering a lot of other material about Power View this Wednesday night May 8, 2013 at the MSBI South Florida User Group event at 6 pm. More info on the event is here and hope to see you all there!