Calvin Ferns' Blog

KPI’s in Power View and DAX Query for KPI’s

I was recently approached by a client where we have assisted in implementing a Microsoft Analysis Services tabular solution and a supporting management pack of SSRS reports.  They were asking about utilising some of the KPI’s defined in their tabular cube in the SSRS reports using DAX queries.  I was at a bit of a loss and most web help seemed to suggest it couldn’t be done:

Blog comment from 2012

However, with a bit of time to investigate it appears Power View does now support Tabular KPI’s and as such a DAX query should be able to pull them out. 


My KPI base measure is named “Highest Ranking” and I am able to view its value, status or goal in Power View.  A little work with every SQL developer’s best friend Profiler and I had what I was looking for.  It appears that when you create a KPI SSAS is defining some measures in the background which do some of the work of the MDX KPI functions.

Therefore the following DAX query against my cycling Demo cube contains the expected results and could therefore be used in SSRS reporting.



"Highest_Ranking", 'FactRanking'[Highest Ranking],

"Highest_Ranking_Goal", 'FactRanking'[_Highest Ranking Goal],

"Highest_Ranking_Status", 'FactRanking'[_Highest Ranking Status]


ORDER BY 'DimRider'[Name]


I also tested for the existence of other KPI functions such as Trend and Weight but these do not appear to be present.  It is also interesting that the use of a KPI over a measure does not change the measure name but just group it in the field list and as such there is no need for a value function.


For more info on KPI’s in Power View there is additional documentation here.  I am currently unsure of the required versions for this functionality so if it is missing from your environment I would love to hear from you.

Power BI preview– Mobile Integration

I have had the invite through to view the Power BI preview today (Get yours here).  The first thing I wanted to do was get some reports up and start displaying them through my Surface and its Power BI App.

The app comes pre-installed with samples but to link it up with your own site you need to hit browse from the app menu.  From there you can add a location.  What caught me out was where do i navigate to to get my Power BI preview reports?

Well it is hidden away in the documentation here that you don’t try and navigate directly to your Power BI site as I did but you directly give the SharePoint site and it will pick up the Power BI app from there. 

Power BI mobile app URL address box

From there you can navigate to the reports you have uploaded to your preview site and favourite the ones you want to appear on the home screen.  The couple I have on there can be seen here:


You can control which sheets get displayed by changing the browser view options of the workbook, as described here

More to come on Power BI shortly..

HP Business Decision Appliance–PowerPivot in a box!

Further to my blog post about SQL server appliances Microsoft and HP also offer the very exciting Business Decision Appliance.  This contains a preconfigured environment with:

  • Microsoft Windows Server 2008 R2 Enterprise Edition
  • Microsoft SQL Server 2008 R2 Enterprise Edition with PowerPivot integration for SharePoint
  • Microsoft SharePoint 2010 Enterprise Edition Prerequisites for SharePoint and PowerPivot
  • Appliance Administration Console
  • Appliance-specific SharePoint Home Page
  • Up to 80 Concurrent Users

The aim of this appliance is to provide a safe and scalable environment for business users to quickly put a PowerPivot environment in place.  This is isolated from existing systems and can be implemented by a business department with very limited IT involvement.  Its isolated nature is important as many organisations don’t run SharePoint 2010 throughout their enterprise.  PowerPivot authors also need Excel 2010 but once reports are built and deployed they can be shared throughout the business through SharePoint using any web browser.  Anyone who has already tried to set up an integrated SharePoint and PowerPivot environment will know that the installation is not simple. This appliance takes that pain away with a one click installation from first start up that can have you up and running in under an hour.

The key point is that business users love PowerPivot and its ability to quickly use their data to answer any question.  They can respond dynamically, collaborate and share insights throughout the organisation.  Importantly PowerPivot allows the creation of reports that look great and are very fast.  All this is done from within the familiar Excel interface and requiring little training to get started.  Any team of business analysts would likely have a massive boost in productivity from the installation of the BDA in their department.  IT can then monitor those reports that are heavily used and decide whether they need making more robust through transition to enterprise software such as Analysis Services.  PowerPivot use is at its easiest in an environment where there is a clean data warehouse but where the business aren't happy with the speed or responsiveness to change of their current front end.  In an environment with more disparate and dirty data the end users need to be more SQL and data modelling skilled, but the payback can be even greater.

I see massive opportunity in this appliance for any team of data analysts to be able to deliver massive value to their business right now. 

More Info on the BDA