Dynamic format using DAX

Just a quick blog post today. Someone had a scenario where they wanted to have a different number of decimal points based  on the items on the rows. Initially they wanted to go with a calculated column but it is much easier to do this using the FORMAT function in DAX. Using the FORMAT function you can return the numerical value any way you want it.

Let’s take the following example. I have the several different devices and I want to format them all differently. I can write a single measure that return different formatting for each device.

This would be the measure I would write:

Measure = if(HASONEVALUE(Device[DeviceName]),
,”Aircard 1″, FORMAT([Sum of Revenue], “0.0”)
,”Aircard 2″, FORMAT([Sum of Revenue], “0.00”)
,”Aircard 3″, FORMAT([Sum of Revenue], “$0.000″)
,”Aircard 4”, FORMAT([Sum of Revenue], “0.0000”)
,FORMAT([Sum of Revenue], “0”)

As you can see the results are what we expect:


You can also use this to show different symbols as you would use this when doing currency conversion.

One thing to observe here is that thanks to several performance optimizations the use of IF and SWITCH in Power BI desktop is significantly improved as compared to Power Pivot for Excel 2013 and SQL 2014.


DAX now has variable support!

With the latest release of the Power BI designer that now supports measure creation we also snuck in another feature that is very useful in complicated measure scenario’s and for performance optimizations. DAX now supports variables. Lets take a look at what that means.

Read more »


New version of the Power BI designer now supports DAX

As most of you probably have seen or heard, the Power BI designer now supports some of the Power Pivot features like DAX and renaming columns, formatting etc.

Some of the features:

  • Formula bar: Using the new formula bar in the Report view you can create measures by writing formulas in the DAX language. The formula bar helps you write these formulas by differentiating functions, fields and measures using syntax coloring, it provides intelligent function and field suggestions and tells you if parts of your DAX expression are wrong using error ‘squiggles’. It also allows you to use multiple lines (Alt + Enter) and indentation (Tab).
  • Comments: The formula bar also allows you to write comments as part of your measures, just type “//” and everything after these characters on the same line will be considered a comment. You can also use /* to add your comment */ in the middle of a formula.
  • Saving incomplete measures: How often are you in the middle of writing a complex Measure and wanted to ask your colleague for help but do not have a way of saving this half-written measure? The Power BI Designer now allows you to save measures that are invalid so you can just save your file and take it to your colleague, or continue to work on it another day.
  • New DAX functions:  New functions are added like Percentile, Median, Contenate, XNPV. For the full list check out this help topic.




For the full list check out the Power BI blog post that talks about all features.

And of course download it here right now:


Pretty excited about this! and I hope you are as well :). I will be blogging on some of the use cased for some of the DAX functions soon.


Sessions at SQLBits

I am very happy to share that I’ll be doing several sessions at the SQLBits conference this year.

I will be doing the following sessions:

  • We now have an exciting developer story for Power BI, the session Developing for Power BI I will go into how to write applications for Power BI and show you several examples. Lot’s of code here, from PHP to .Net, JSON and REST and OAuth.. not your average BI session Smile.
  • The BI Power Hour will be done for the first time outside of a pure Microsoft event, ill be doing this together with Power Hour superstars Matt and Matthew:  BI Power Hour
  • Lastly I will do a Pre-con together with Matt and Matthew where we will take an entire day to take you through the entire Microsoft data stack from Azure Data Factory and Azure Machine learning to Power BI. This is going to be a great way to see how all of these services work together and get a in depth overview of what all these services contain.

Looking forward to seeing you there!


Power BI news

You probably have heard the news already but we have some exciting information that has been released on the new Power BI service as you, for all the details read the blog post on Microsoft.

Some great new functionality that is being released are:

  • – Dashboards that allow you to gain insights in your data right away. A single pane of glass for all your data. You can mix and match data from several data sources onto a single dashboard.
  • – Many new datavisualizations like treemap, fill map and many more.
  • – An amazing Ipad  app to connect to your data
  • – Out of the box connectivity to many data sources like SalesForce and GitHub.
  • – Hyrbrid connectivity, meaning connect to your on prem tabular models and visualize using the latest and greatest data visualizations in the cloud.
  • – Developer API’s that allow you to create datasets and push data into Power BI. (see http://dev.powerbi.com for more details)
  • – Last but not least is the new Power BI designer that will merge Power Pivot, Power View and Power Query into a single experience that will focus on making analytics visual and easy to do.

Of course this is a preview release so there is much more to come soon for all this functionality.

And of course the last very important item is the price:

Today, anyone with a US business email account can try the preview of the new Power BI for free. We’ll expand to international users in the future. When we reach general availability we will introduce a free offer, available as Power BI. We’ll also introduce Power BI Pro, available at a low monthly price, with additional data capacity and features for the enterprise. In anticipation of this move, on February 1 we are lowering the price of our in-market Power BI service to $9.99 per user a month, a 75 percent reduction in price. More information on these offers is available here.

I have a few blog post I am working on to cover some new functionality for the designer and the developer API’s that I’ll finish soon!.

Now go to http://www.powerbi.com/dashboards to go and sign up yourself.

Update: I got some questions about what happens with existing Office 365, the straight answer is that existing Power BI for Office 365 customers will be able to transition to the Power BI Pro experience when it is made available using their existing subscription license. Additional details will be made available as we approach general availability. For more details see this blog post: http://www.microsoft.com/en-us/powerBI/licensing.aspx


Loading an image into the Model using Power Query

Last week I had an interesting questions, someone wanted to show images in Power View but didn’t want the report to have to fetch the images every time. So the images needed to be loaded into the model.

I have this simple Device table that I loaded from Azure into Power Query:



Read more »


New year and several updates

As most of you have probably noticed I have not been very active recently, this has everything to do with the recently released preview for Power BI that took all my time. Luckily now that we shipped the first preview I hope to be able to spend some time on blogging again, mostly on the amazing new designer when more features come online for that.

Right now I am playing with the Power BI API’s in the hope that I can write a Power BI for WordPress plugin that allows anyone to track their usage using a wordpress plugin by directly  psuhing visitor statics into Power BI, more on this in a later blog post :).

I also updated the commenting system to use disqus, the out of the box wordpress commenting system is not great. This plugin looks great imho :) Let me know if you have any issues or comments on the new commenting system.

Happy new year!



Automatically ranged date table using DAX and M

I am prepping some demo’s for my SQLPass summit session “End-to-End Demos with Power BI” where I’ll show many of the tricks I use in my Power BI workbooks.

One of them is this trick that I recently figured out, I am so excited about it that I wanted to share it with all of you. I figured out a way to automatically generate a data table with a dynamic range based on the data in the fact tables.

Read more »