skip to Main Content

Power BI Desktop New Feature: Bi-Directional Relationships!

Guest Post by Andrew Todd

Intro by Avi: We’ve been excited about all the new functionality that Power BI Desktop brings to the table. Bi-Directional Relationships is one of those new features.

Before this (and still for the users of Excel+PowerPivot), you could force a relationship filter to flow “uphill” and implement many-to-many relationships. However that was done inside your DAX measures (click to read a detailed how-to). Now, with Power BI Desktop, that can be done automatically via bi-directional relationships. Andrew shows us how, using some Dynamics CRM data…

image
Dynamics CRM: Want to find out which sales calls resulted in a sale? You can with Bi-Directional Relationships in Power BI Designer

Note: Download the example .pbix at the bottom of this this post, which includes the above dashboard, example bi-directional relationships setup and showcasing other new features in Power BI Desktop

Lookup tables in PowerPivot are like reservoirs holding torrents of instructions poised to break free at the click of a slicer tile. When a user clicks on a slicer connected to a lookup table, they open a flood gate and instructions are unleashed to flow downhill to data tables.

In Power BI Desktop, filters can defy gravity! Not only can filters flow downhill from the reservoir into the data tables… they can also flow uphill from data tables to lookup tables! Those instructions flowing uphill into the lookup tables can then spill over to data tables on the other side of the lookup table!

Driving Sales Activity Metrics from the Back Seat

With filters flowing uphill, the filters from the data table side of the relationship can be sent back to the lookup table. In a sense – those filters can actually flow right through the lookup table and down to data tables on the other side! The lookup table itself is filtered and the context ‘splashes’ over to the other data tables.

Power Pivot, PowerPivot, Power BI Desktop

Read the Rest

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