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
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:
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.
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).
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.
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.
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.
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.