“Beta Test” the New Reference Card?

April 7, 2015

Post by Rob Collie

Power Pivot / Power BI / DAX Reference Card

RefCard Pt 2: Captures/Condenses Important Tips & Fundamentals from Our Years of Teaching
(Five “Sides” Pictured Above, Sixth “Side” is Still in Development)

PDF Preview Available Later This Month

Short Version:  drop us an email at the address below, and later this month we will send you a preview of the new reference card in PDF format.

Drop Us a Note to Get Your Preview Copy

“Completing” the First Ref Card

Creating the first reference card might be the smartest thing we’ve ever done. 

We can say that without hubris, because it was actually NOT our idea, it was Bill Jelen’s.

Still though, we have the wisdom to recognize Bill’s genius.  The reference card has been a CRITICAL component of our classroom teaching for years now:

Power Pivot / Power BI / DAX Reference Card

Ref Card Part 1 (Laminated Version) is Only $2.95 at MrExcel.com – Click Image
(Preview Participants will Also Receive a PDF of Part 1)

Final “Product” is TBD

1) Given that the new reference card is six sides, it ideally would be printed in some sort of “tri-fold” laminated format, and in full color of course.  That’s might be pretty expensive to produce, so we’re still noodling on how to do it.

2) Plus, it would make sense to merge it with Part 1 to form something more like a quad-fold.  Hmm.

3) We want your feedback on Part 2 so we can improve it before we do anything rash like print 5 thousand copies.

4) I’m also contractually obligated to say that we WILL improve the aesthetics of Part 2 as well.  Because, well, *I* am designing Part 2, and while I might be a decent communicator, I’m not exactly a graphic designer.

Anyway, Sign Up for the Free Preview PDF Smile

Drop Us a Note to Get Your Preview Copy


The Diabolical Genius of “SWITCH TRUE”

March 31, 2015

Post by Rob Collie

SWITCH TRUE Alternative to Nested IF's

Did Someone Say Deliberately “Misuse” a DAX Function for Our Benefit?  We’re IN!

An End to Nested IF’s?  Sign Us Up!

When we first saw the SWITCH function make its debut in Power Pivot a few years back, it was a “hallelujah” moment.

Whereas we used to have to write nested IF’s, such as this:

   IF([MyMeasure]=1,expr1,
      IF([MyMeasure]=2,expr2,
          IF([MyMeasure]=3,expr3,…)))

Now , with SWITCH, we could write that much more cleanly as:

   SWITCH([MyMeasure],1,expr1,2,expr2,3,expr3…)

Which do you prefer?  It’s easy to make a strong “case for SWITCH,” isn’t it?

But What About Cases Other than Equals?

Now, let’s consider the following nested IF:

   IF([MyMeasure]<1,expr1,
      IF([MyMeasure]<2,expr2,
         IF([MyMeasure]<3,expr3,…)))

Notice that we’ve swapped out “=” for “<”.

And we can’t do that as a SWITCH, because SWITCH checks for exact matches between [Measure1] and 1 (or 2, 3, etc.)

This is unfortunate, because in these cases, we’ve had to keep using nested IF’s.  And wow do I (Rob) *hate* nested IF’s.  I can never seem to match the parentheses up correctly on the first try.

But There’s a Sneaky Antidote!  We CAN Still Use SWITCH!

Read the rest of this entry »


Power Pivot and Basketball Superstars: Many-to-Many and USERELATIONSHIP

March 27, 2015

By Avi Singh [Twitter]

Friends at a company play pick-up basketball during their lunch hour. Since there are no established teams, players can be randomly matched up. But these folks happen to be engineers/data-nerds, so they keep detailed track of games, teams, players and win/loss. The diagram view of the data is shown further below.

Question: How can we determine which player pairing is the most successful?
Since players are randomly teamed up, are there combinations which when teamed up have an unusually high winning percentage?


Word Cloud of Player Nicknames: Size of text indicates number of games played

Application: This would naturally extend to other sports, but I believe may also apply in many non-sports scenarios, where items are paired up somewhat randomly (or by design) and we want to know how effective those pairings are.

Thanks to Kirill Perian (basketball nickname K-Real), an attendee of one of our past webinars, who sent us the dataset and posed this question. Dataset has been simplified to showcase this scenario and anonymized to protect the identity of the losers :-)

File can be downloaded here.


Model Diagram: Showing Game, Team and Team Players

First we will address the easier scenario of creating metrics for individual players, using the many-to-many pattern. Next we will take on writing measures to compare performances of pairs of players.

Read the rest of this entry »


Schrodinger’s Pivot, or Why CALCULATE() Should Be Your Favorite Function EVER.

March 17, 2015

Post by Rob Collie

Schrodinger's Cat Has Relevance to Power Pivot and Power BI

In the Classic Physics Thought Experiment, Schrodinger Hypothesized a Cat That Was Simultaneously Dead AND Alive.
(But here we will use the more humane “Simultaneously Green AND Grey.”

Back to Basics!

PowerPivotPro.com celebrated its 5th birthday back in November.  Over 5+ years, we’ve progressively covered techniques with an increasing level of sophistication.  That’s pretty natural – we ourselves have become more skilled over time, AND there’s a tendency to not want to write the same post twice.

But today I want to drive home a basic point, one that will help “recruit” Excel pros into the Power BI camp, AND that will help “crystallize” a few things even for the longtime DAX practitioners.

Schrodinger’s Cat – A Classic Battle of the Nerds

imageIn 1935, physicist Erwin Schrodinger wanted to show Albert Einstein how wrong he was.  Einstein had recently published a paper that made an astounding claim about the nature of subatomic particles.  If those claims were true, said Schrodinger, even “big” everyday stuff, like cats, could also behave in that same outlandish way.  Which made Einstein look kinda silly, in Erwin’s mind.

He proposed the idea of a cat that was both simultaneously alive AND dead, and basically said “See, Albert?  Alive AND dead is clearly impossible, so your theory is junk.”  See here for details.

But modern quantum physicists actually think the cat experiment does NOT disprove Einstein’s claim.  In fact, they think Schrodinger’s Cat demonstrates that the universe is fundamentally a MUCH stranger place than we typically think.

In short, the concept of “impossible” is subject to re-evaluation.

This PivotTable is Simultaneously Filtered AND Unfiltered

Read the rest of this entry »


Dynamic Age Calculation Using Measures

January 29, 2015

by Matt Allington

I was recently helping a forum member at http://powerpivotforum.com.au with a problem about how to dynamically calculate an employee’s age.  I thought a worked through example would make a good blog post as it demonstrates a further use of disconnected tables vs the more common “disconnected slicers” tables that Rob loves so much.

First let me explain the scenario.

Number of Employees Under the Age of 35

The requirement is to be able to calculate the total number of employees under age 35 years of age at any point in time.  The DAX formulae therefore need to take into account new employees starting at the company, employees leaving the company, as well as the fact that all employees get older every year.  Here is the solution I created. Read the rest of this entry »


Repeat Customers in DAX: Three Flavors

December 9, 2014

Post by Rob Collie

Repeat Customers in Power Pivot / DAX:  By Number of Transaction Lines, By Number of Distinct Orders, and Allowing for Cross-Year Return Customers

In 2004, There Were 2,561 Customers Who Bought Something in the Southwest.
But How Many of Those Were Repeat Customers?  Depending on How We
Define “Repeat,” We Can Get at Least Three Different Answers.

A Right Turn at Albuquerque…

I sat down today to write about “Disconnected Cube Formulas” – yes, you heard that right.  A brand new technique that I think has some pretty exciting (yet admittedly narrow) applications.

But along the way, like Bugs Bunny, I ended up doing something at least as interesting.  So let’s do that one first.

Setting Up the Problem

I have four relevant tables:  Territories, Customers, Calendar, and Sales:

image

The first three are Lookup (aka Dimension) tables, and Sales is a Data (aka Fact) table.

Active Customers is a pretty easy formula:

  [Active Customers]:=

   DISTINCTCOUNT(Sales[CustomerKey])

Read the rest of this entry »


Displaying Top N, Bottom N, and “All Others”

November 11, 2014

Post by Rob Collie

image

If We Use Excel’s Built-In Top N Filter to See Our Top 1,000 Customers, It Hides the Other Customers Completely.  But Using DAX, We Can Just “Split” the Audience into Two Groups.

This Came Up Recently…

Hey, I absolutely ADORE the TOP N filter capability offered by all Excel Pivots.  It kicks major booty and we use it all the time:

image

The Top 10 / Top N Value Filter in Pivots:  Get to Know It, It Does Amazing Things

But If we set that to show us the top 10 customers, it shows us JUST those 10 customers:

image

OK cool, we see those top ten customers, and that they collectively purchased $132,026 of stuff from us.

But we want to ALSO see how much the OTHER (non top 10) customers are worth too.

Read the rest of this entry »


New Twist for Dynamic Segmentation: Variable Grain Control

November 6, 2014

By Avichal Singh [Twitter]

Dynamic segmentation or banding has been covered in PowerPivotPro articles in the past and in beautiful detail by the Italians – Marco Russo, Alberto Ferrari (these folks are literally “off the charts!” in Matt’s representation of Power Pivot skill levels :-) ).

It involves grouping your data in specific numeric bands or segments; for example looking at your sales data based on the price range of your products. You have a long list of price points for your products, instead of looking at each price point individually it would be nice to group them into segments say to represent the low, medium and high price items in your catalog.


Hundreds of products at different list prices…  =>  Grouped based on their List Price Range

Variable Range Selection

That is great, however it is hard to predefine segments that would work well in all scenarios. As your data changes over time, or as users slice and dice your existing data (e.g. filter to a specific region, product category or year) the segments may prove to be either too granular or not granular enough. In the case below, the predefined range does not have enough grain or detail and pretty much everything ends in one bucket ($3000-$4000).


Predefined Segment Ranges may prove too granular or not granular enough
as you work your data

What if your segments had a range of options from high to low granularity, so that you could choose the right segments based on the data and your need!


Range can be chosen to show 1000s, 100s or 10s based on dataset

Download File
Watch on YouTube or keep reading…

Read the rest of this entry »


SUM, SUMX or CALCULATE()…Choices, choices!

October 23, 2014

By Avichal Singh (Avi)

When I was working recently with a client, helping her remotely – I asked her to calculate the sum for sales amount in the table. She responded whether she should use SUM, SUMX or CALCULATE?

Simple question, but not a simple answer. Or I can give you the classic lawyer response – “It depends!” Let’s review.

SUM: Simple Unmitigated Magic

The good news is that a simple SUM, would work in majority of the cases. A simple sum in the hands of Power Pivot is a powerful tool. With the magic of relationships a simple SUM can show you tricks you could never have imagined in Excel.

Power Pivot relationships mean, that you define your measures once and use them everywhere.

Power Pivot Measures: Define Once, Use Everywhere“Define Once, Use Everywhere”

Your measures conform to the shape of your pivot, so you can drag and drop any fields from your model, use any slicers and the measures would still work.

Sales:=SUM(Transactions[Amount])

image
Simple SUM() does magic in Power Pivot: Define Once > Use Everywhere in action

Only when you see the results and go…”Uh…that is not exactly what I want”, should you explore other options.

Step at a time – SUMX

Read the rest of this entry »


How about a FIRSTDATE()?

October 9, 2014

Guest Post by Willem van Dijk

Intro

I am, like most (if not all) visitors to this website, an addict for information –> data –> truth –> Εμπειρία <-hope that sounds like Greek to you…

My journey into Power Pivot has been one of uphill struggles and downhill thrills. It’s been brief moments of ego feeding, immediately followed by lengthy bouts of meekness. One moment I am Indiana Jones going for his gun in The Raiders of the Lost Ark directly followed by the same scene in The Temple of Doom


Where did my DAX go?

Read the rest of this entry »


Blending “Time of Day” Analysis with Calendar/Date Analysis

October 7, 2014

Post by Rob Collie
 
Blending “Time of Day” Analysis with Calendar/Date Analysis in Power Pivot and Power BI

Our “Morning” Website Traffic is Down 21.5% in Jan 2014 vs. Jan 2013, But “Evening”
Traffic is Up by a Similar Amount, and Full-Day Traffic is “Flat” at +0.9%

(Fake Data, But Real Analysis)

Two Different Flavors of “Time”

Usually, when we talk about “time” in Power Pivot, we’re talking about the Calendar/Date flavor:  How much have things changed from yesterday to today.  What are our Month to Date numbers, and how do those compare against the same period last year?  Let’s call this “macro-trending.”

But time of day is also often interesting:  what are the trends WITHIN a day?  Let’s call this “micro-trending.”

And then, the hybrid of the two:  how are our “micro” trends changing over the course of the year, month, quarter, etc?

I don’t think the techniques here are terribly complicated, but they might be a little difficult to conjure up on your own.  So, it’s time for a post – and a downloadable workbook! Smile

The Key:  Separate the Date and Time Components!

Read the rest of this entry »


5 Interactive Chart Techniques Come Together

October 2, 2014

by Matt Allington

Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from powerpivotpro.com, from searching the Web, as well as some of my own ideas.  While some of the techniques I will talk about in this post are not new, I have combined some of these old favourites with some new techniques to solve some of the problems I have come across. I want to illustrate how the combination of these things can deliver a very positive user experience, and just as importantly – anyone with a good set of Excel skills can build an interactive charting tool like this by following the patterns demonstrated.  I have created a demo of all of these concepts into a new workbook using Adventure Works so that you can see how these techniques come together for the user.  There is a link to this workbook at the end of this post.

The techniques I have used are:

  1. Disconnected slicers used to create interactive chart series
  2. Cube formulae and standard Excel to make an interactive chart title

I love these 2 tips I learnt from Rob – so user friendly.  However I came across a few problems when I tried to implement these, hence I have developed the following 3 additional techniques to solve these problems.

  1. Cube formulae and standard Excel to make an interactive legend
  2. VBA and “link to source” for interactive axis formatting
  3. Excel VBA to change which Axis the series appears on.

I have created a short video to demonstrate the 5 features built into this workbook, and I then explain each of these in more detail below.  I have not hidden the behind the scene workings so you can see these in action – of course you would normally hide these from the user.

Now let me call out the key techniques I have used to make this workbook rock.
Read the rest of this entry »