skip to Main Content

Counting Overlapping/Shared Twitter, Facebook, Instagram, etc. Followers

Post by Rob Collie

From Last Week’s Client Work

Last week a client asked us to solve a somewhat unusual problem:  given any two lists of Twitter followers, tell us how many followers “overlap” between the two lists.

Two Lists of Twitter Followers:  How Do We Find the Overlap Using Power Pivot / Power BI / DAX?

How Many of Han Solo’s Followers Also Follow Leia Organa, and Vice Versa?
(Randomly-generated Twitter handles are funny.  I particularly like “@Gommo” and “@Xxfok”)

Loading the Data:  Using Power Query

Let’s use Power Query to perform the import this time, both because we’re using PQ a lot more around here now that we have Power Update, and because we’re gonna need PQ for the more complex steps later.

Note that all of the steps below are performed using Excel 2013.  (I find Power Query to be a bit too clumsy in Excel 2010.)

Power Query, aka Power BI Data Import

Importing from a Table Using Power Query:  Step 1
(Unchecked “has headers” because of the “Han Solo’s Followers” Row)

Read the Rest

DAX and Basketball Superstars: Many-to-Many and USERELATIONSHIP

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

A mystifying and awesome solution for many 2 many

imageRip van Winkle wakes up and looks around

I have a confession to make:  when it comes to technology, I’m a much better writer than reader.  I don’t pay nearly as much attention to what everyone else is saying in their books and on their blogs.  I’m too hunkered down in my own little world, figuring things out my way, to notice all the good stuff that may be going by.

Sometimes you just have to accept who you are though.  The way I work…  works for me.  I grind away on things, like erosion, until a simple picture emerges.  (And then I screenshot that sucker with WinSnap and throw it on the blog next to a movie quote, heh heh.)

Well something got into me recently.  I started checking in on SQLRockstar’s blog a bit more often, because MAN – that guy puts together a simultaneously technical and human series of posts, with ridiculously high production values.  I don’t know SQL and I don’t intend to learn, but I read anyway.  I like to think what’s happening on his site is similar to what happens here:  tech is being yoked and bent to serve humanity, not vice versa.

Oh and you definitely should check out his Disclosures page.  I mean, what if he were part owner of the gentlemen’s club whose explosion he covered?  I’d want to know that, and it’s very responsible of him to come clean up front so we can all read with confidence.  Don’t you feel better?  I do.  Here, let me try it.  Disclosure:  Tom is planning to review my book.  I feel so clean!

But I did something else the other day too.  I actually Googled something about PowerPivot.  (I know!  A big step for me!)  And I found something AMAZING.

Read the Rest