I was working on some measures for a tabular cube the other day, but as you’ll know if you’ve used Visual Studio to edit or create measures in a tabular cube, the terrible textbox you have to edit your code in is awful. So I decided to have a play with DAX studio. and see if it was easier and simpler to write my measures in there first.

DAX studio is a neat little application which allows you to write/test/format your DAX queries, the problem was that it is geared up to writing DAX queries, not editing or creating measures in a cube. Not being an total expert in DAX this proved to be a bit frustrating until I read about the ROW() function. This little bit of code will allow to you just get a single row result for your DAX query. So you can use this to ‘test’ your measure before pasting it back into Visual Studio by encapsulating your ‘measure code’ inside a single ROW() result.

For example if my measure in SSDT (SQL Server Data Tools) was this piece of DAX:-

MyMeasure := CALCULATE(
  COUNT(Person[Person Key])
)

Then all you need to do is put that code inside the ROW() function like this and you can execute this in DAX studio:-

EVALUATE

ROW("MyMeasure", CALCULATE(
  COUNT(Person[Person Key])
))

Just remember to only copy the calculation part of the ROW() function back to SSDT for your measure. For complex measures this is a much better way of editing and testing in my opinion.

Advertisements