Did you know that you can enable passing multiple criteria in DAX to filter a calculated value in a column or measure?
Sometimes reporting data values day-by-day can create the impression of dramatic ups and downs, when really what we want to see is the general trend of changes over time. We need, in other words, to create a chart based on moving averages. Such a measure is commonly used in tracking the price of stocks, but there are many other cases where showing, for example, 7-day moving averages is preferable to just showing the value for a given day. DAX in Power Pivot can handle this calculation quite easily. Here’s how, using the EARLIER function
Clients often ask me what's needed to guarantee user adoption on a new or re-designed CRM system. Over the years, I’ve whittled my response to these three essential elements.
Last fall, I attended SQL PASS in Seattle and learned about DQS (Data Quality Services) that Microsoft released with SQL 2012. It was perfect timing (I was in the middle of merging several third party data sources, and we'd decided to use MDS to maintain our domain data). This blog is a brief description of how I implemented DQS for our environment.
The more I work in Power Pivot, the more I’m amazed at what can be done using DAX in Excel, Microsoft’s powerful expression language for creating custom calculations, aggregations, and references within and across data entities. In this post, I take a look at the very useful LOOKUP VALUE expression, for when you want to scan any table in your data model, find a specific value, and relate that value to the current row.
In a previous blog post by Brett Stewart, he introduced the geo-coding capabilities that utilize the Map visualization feature in Power View add-in for Excel 2013. In this post, we'll look deeper into how to produce visualization reports with Excel 2013 Power View.
In my last post, I explained how to create a Calculated Column that aggregates values in the Power Pivot data set for rows that meet specific filter criteria (by using the CALCULATE, SUMX, and ALLEXCEPT functions, you can customize exactly which rows you want to have included in your sums for a column). In this post, I explain how to do a distinct sum on that column, where you specify the values that you want to have included in the totals. We’ll look at how to use a DISTINCT so that you don’t double count duplicated data.
With the lifecycle of SQL Server 2012 in full swing, many companies are looking to upgrade their 2008/2008 R2 instances in order to take advantage of what 2012 has to offer - especially now that SQL 2012 Service Pack 1 (SP1) has been released with its additional new BI specific features.
One of the greatest benefits of creating a PowerPivot or tabular model is the ability to easily build custom calculations, using Data Analysis Expressions (DAX). In this post, I explain some of my favorite DAX expressions in the context of a common scenario – calculating the percentage of an individual value in a data set relative to a total value derived from summing across rows that meet specific filter criteria.
“Big data”. “Predictive modeling”. “In-memory analytics”. There are a lot of new technologies out there in the business intelligence space. But, as a business person with relatively basic reporting and analytic needs, I ask all my IT peers to give another trend more attention – Collaborative BI.
Microsoft’s PowerPivot add-in to Excel (2010 and 2013) is a powerful tool for business users to combine multiple types of data sources into a single pivot table. Learn the top three reasons to deploy PowerPivot to SQL Server Analysis Services (SSAS).
Getting formatting errors when importing records from Excel into DQS? Here's a process that might help you.
Like most of Microsoft’s prominent consumer products, 2012 was also a banner year for the Server and Business Tools groups – with Office 2013, SharePoint 2013, and SQL Server 2012 SP1 being the notable items. Also, just like the consumer tools, the server/business tools have become much more integrated in this release cycle, with Office 2013 and SharePoint 2013 leading the charge. Excel Services – a part of SharePoint Enterprise since 2007, is leading this welcome trend.
After importing our mapping data into DQS, the business I was working with wanted to verify that all the values were mapped/matched correctly. We had a lot of data, so scrolling thru the UI to visually test was not realistic, and we'd also need an export of the mappings. Here's how I did it.
SQL 2012 SP1 has some great features, one of which I saw at SQL PASS this past week, DQS (Data Quality Services).
Learn how Power View further realizes the “self-service” promise of the Microsoft BI stack.
Starting in SSRS 2012, installation has been greatly simplified. Here is a guide to configuring the new way of setting up SSRS 2012 in SharePoint 2010.
In the Windows Phone 7.1 SDK , Microsoft has included a number of template start-up projects to accelerate development on the Windows Mobile platform.
Front office / back office integrations set my teeth on edge. For anyone that has spent a significant amount of time working on them, you know that they often elicit fear from those involved.