Power Pivot

Live tweeted #sqlpass’s Business Analytics VC webinar: 13 Excel Tips!

Standard

I was live tweeting during our monthly PASS Business Analytics VC meeting, Here are the tweets to learn about 13 Excel Tips!

Thanks everyone who attended, I hope it was helpful!

Here are some ways to follow the Virtual Chapter:
Website: http://bavc.sqlpass.org/
Youtube: https://www.youtube.com/channel/UCOiRAA4gBxEeVxwmEZ1qy1w
Twitter: https://twitter.com/passbavc
LinkedIn: https://www.linkedin.com/groups/PASS-Business-Analytics-Virtual-Chapter-6701113

Power Pivot: How to get Month Name from a date field?

Standard

Problem:

How do you get a Month Name from a date field in Power Pivot?

Solution:

here’s a code snippet that should help:

[code language=”SQL”]
=FORMAT([date],"MMM")
[/code]

This should give you month names (Jan, Feb, …) instead of integers that are returned by the MONTH function.

couple of notes:

1. date field needs to be used to get the month name

2. MMM needs to be in uppercase.

I hope this helps.

Licenses required in Excel 2013 & Office 365 for Power View and/or Power BI:

Standard

Here’s a quick chart that I created based on reading up from different sources about licenses required in office 2013/office 365 to use Power View and/or Power BI. It was not straight forward and I had to use multiple sources to find this information, so hopefully this helps you:

Power View Power BI Excel 2013 License

Please Note:

Double check w/ official sources. The chart is meant to guide to find the right license for your needs. Please use this as a starting point & use official resources before making a purchase decision.

Sources Used:

http://www.microsoft.com/en-us/powerBI/pricing.aspx#fbid=ygmkLW5EEH9

http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2013/02/27/unable-to-use-excel-2010-powerpivot-workbooks-in-excel-2013.aspx

http://sqlblog.com/blogs/marco_russo/archive/2013/08/19/excel-2013-stand-alone-now-includes-powerpivot.aspx

http://office.microsoft.com/en-us/excel-help/power-view-explore-visualize-and-present-your-data-HA102835634.aspx

http://cwebbbi.wordpress.com/2013/02/01/office-2013-office-365-editions-and-bi-features/

PASS BA VC: Analyzing Road Traffic Accident Data in #PowerBI by Chris Webb #sqlpass

Standard

Join PASS Business Analytics VC on Thu, Mar 27 2014 12:00 (GMT-05:00) Eastern Time (US & Canada) for “Analyzing Road Traffic Accident Data in Power BI” by Chris Webb. In this one hour session, Chris will spend an hour going over the details on how he developed his amazing demo for the Power BI contest (see here & here).

here’s the link to Register: http://bit.ly/SQLPASSBAVC

Demo:

We hope to see you there!

Paras Doshi
VP – Marketing, PASS BA VC

Three Power Pivot Installation FAQ’s:

Standard

Q1: How Can I upgrade Power Pivot on my machine?

A1: Uninstall Existing version (Yes, it’s not intuitive, but you have to uninstall existing version). Download the version that you want to upgrade to. Install it.

Q2: I’ve SQL 2008 R2 on my machine. Can I install “Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010”?

A2: Yes, go ahead! “SQL server 2012 sp1” in the name is confusing 🙂 It doesn’t matter which version of SQL server you’ve on your machine – In fact, Power Pivot does not need a SQL server to be installed on your machine.

Q3: How do I check Power Pivot version?

A3: Excel > Power Pivot > Settings > You’ll see version # on the top part of the screen.

Power Pivot Settings Excel

I hope this helps!

Time Intelligence in DAX: Last n days

Standard

Problem:

How to formulate Last n days in DAX?

Solution:

It can be achieved using the combination of CALCULATE & DATESINPERIOD function.

Formula to sum up values for last 10 days

[code type=”sql”]
MeasureName:=CALCULATE(SUM([YourColumnName]),DATESINPERIOD(Calendar[DateKey],TODAY(),-10,day))
[/code]

Note the use of a calendar table in the DATESINPERIOD. It is pretty common to have a “date” dimension & I’ve used it the DateKey from the dimension as the “date” column which is required by the DATESINPERIOD function.

You can use the above example as a starting point now.

Power Pivot DAX: Difference between two DATE values

Standard

Requirement:

Take difference between two data values.

Example:

EndDate: 11/20/2013

StartDate: 11/14/2013

DateDifference: 6

Let’s start writing some DAX!

Ok, seems simple, right? Try creating a measure DateDifference:=[EndDate]-[StartDate]

Did that work? NO? Does it return date?

Don’t worry, Here’s the solution. Try creating following DAX Measure:

[code language=”sql”]
DateDifference:=1.0*([EndDate]-[StartDate])
[/code]

That’s about it for this post. Here are some related Posts:
Calculate the difference between two dates in DAX
Q: How can I calculate difference between two dates in DAX (seconds, minutes, hours, days and months)
NETWORKDAYS() Equivalent in PowerPivot?

Power Pivot: Casting DateTime to Date in SQL Server source query

Standard

DateTime columns can be tricky for analysis purposes. They don’t work well with Pivot Tables because of the time part, each value seems unique to the Pivot Table & it also creates problems while creating relationships with Date Dimensions. And so, It’s a common need to convert them to just Date before analyzing data & also a common need to create a relationship between the Date (and not DateTime) with Date Dimension Table.

So if it’s possible, I try to do the data type conversion in the source system query. If your source system is SQL Server, you could use this piece of code:

[code language=”sql”]
select [your-fields],cast([DateTime_Col] as date) as Date_Col from TableName
[/code]

Doing the data type conversion upfront in the source system query is a good thing to do. And I hope this is helpful.

Related Posts:
Strange date relationships with #PowerPivot
Date and Date/Time – Sneaky Data Types!

 

Example of an DAX Substring Equivalent:

Standard

DAX (Microsoft’ Data Analysis Expressions Language) does not have a Substring function but I needed something like that for the following problem:

I had domain/username as input and I needed to extract just the username part of the string.

Input format: domain/username

output format needed: username

Input column name: UserID

so here’s the DAX formula I used: RIGHT([UserID],LEN([UserID])-SEARCH(“”,[UserID]))

Note: The Formula is shown for demo purpose only, It may not work directly before making appropriate changes to the formula like making sure the column name is right & If the double quotes show problems, try deleting it & typing them back again.

Note that I combined some of the available DAX text functions to achieve what I was looking for. There might be other way to do this and I would be happy to learn about it too. Meanwhile, I hope if you reading this, this might give a good starting point while researching your DAX problem.

How to add additional Columns to an Existing Table in a Power Pivot Model?

Standard

Problem:

There’s a Power Pivot Model. It imports some number of columns from a table. Now, there’s a need to import an additional column to this table. How do you do that?

Solution:

Here are the steps:

1. Open Power Pivot Model.

2. Go to Design Tab.

3. Click on Table Properties:

Power Pivot Table Properties

4. From here, You can add additional columns:

Add Columns Table Power Pivot5. click on save and that should add the column to your model:

Power Pivot Edit Connections TableFAQ:

#1: I can’t see what you see inside Design Tab. What do I do?

Make sure you have the latest Power Pivot version. You can read this: http://parasdoshi.com/2013/06/04/excel-2010-how-to-check-the-installed-version-of-powerpivot-and-whats-the-latest-version/

#2: How about changing the data source & pointing to new cube/database? It’s very helpful when you switch between QA, Dev or PROD servers. Here are the steps:

you can do that too! Go to Design > Existing connections > Select Connection > Edit > Make changes > Test it! > Save > “Refresh”. Any Problems? No? Great. That’s Done!

Related Posts:
How to add a column in an imported Table while developing SQL Server 2012 Analysis services Tabular Model