VLOOKUP better than Relationships? Hell must have frozen over

April 23, 2015

By Dany Hoter

We always think about a data model with multiple tables and relationships between them as a big improvement over the common practice of combining tables using VLOOKUP expressions.
(Watch Video for more: PowerPivot Relationships are EASIER than VLOOKUP, not just faster)

I came across a customer request that forced me to admit that in some aspects a VLOOKUP is more flexible than relationships. To solve some specific scenarios we have to use modeling methods which are not elegant and not efficient.


VLOOKUP better than Relationships?

I hope that some of the readers will know to defend the cause of the data model and find a magic solution that is both elegant and efficient.

How can good old VLOOKUP be better?

A solution based on VLOOKUP is using a separate formula for every row, this is what makes it cumbersome, slow and error prone – right?

Yes but this is also what makes it more flexible in some cases.

Let’s describe some reasons why is it more flexible and give some examples:

VLOOKUP can use a different way to find the right row based on the input value

=IF(A5=””
, VLOOKUP (A6,lookuptable6,3,false)
, VLOOKUP (A5,lookuptable5,4,false))

If lookup value is empty in our table, there is no point in looking for it in the lookup table and instead a different lookup table is used and a different lookup value is searched for.

For example (download), if a state column is empty or contains “NA”, return a value from the countries table using the country as a search value.


VLOOKUP gives us the flexibility to lookup State or Country names 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 »


Manipulating Relationships in VBA (in 2013)

June 10, 2014

Guest Post From Dany Hoter

Intro from Rob:  Ah, the international man of mystery returns!  My first instincts when I think of Dany Hoter, other than “one of the most fabulous humans I have ever known,” generally can be summarized as “MDX and Cube Formulas Monster.”

But he’s far from a one-trick pony.  Generally speaking, he has a level of tenacity and patience rarely encountered outside of laboratory conditions.  Couple that with an insatiable drive for The Right Thing, and you get some crazy results.

Today is one such CRAZY example.  Simultaneously, he shows us how to compensate for a drillthrough bug, AND delivers a working example of relationship manipulation via VBA macros.

THIS IS AN ADVANCED TOPIC POST.  Feel free to skip this one.  This is the deep end of the pool and even I don’t swim in these particular waters yet.

Note of course that this technique is 2013 only, and will not work with Power Pivot in 2010.

Take it away, Dany…

A Drillthrough Bug with Inactive Relationships

I started this VBA project after one of our partners wrote to me about a customer complaint regarding inactive relationships in Power Pivot:

image

The Sales Table has TWO Relationships to Calendar – One is Based on OrderDate (Active), and the Other is Based on ShipDate (Inactive – Dashed Line)

 

Read the rest of this entry »


Reverse Polarity RELATED()

October 3, 2013

 

Guest post by Ken Puls

I was recently working on an interesting thing in PowerPivot, and thought I’d share it in case it may help someone… A quick summary of the issue is that I needed to examine multiple records in a child table, and return a single result to the parent table if a condition was present; something that goes against the normal relationship flow.

Scenario Background

I have a file that tracks the purchase and sale of land, as well as the respective property taxes and assessments that we get on an annual basis. It is set up like this:

9-17-2013 2-56-59 PM

At first glance this might look complicated… Each Parcel is assigned a unique Parcel Identifier, known as a PID, a key identifier that will never change for a piece of property.  This info is then used to identify that parcel by the tax authority, the assessment authority, and our appraisers and accounting systems.  In the case above, we can see that the PID from the Parcel table links to the Transaction, Taxes and Assessments tables. From the other side we’ve got the Key_Date that links back to the three tables as well, although it links to TranDate, TaxYear and AssessYear respectively.

Read the rest of this entry »


The Dreaded “One to One” Relationship, or Why “Boaring” Movies Are Best

June 4, 2013

Another Example of “Relationship May Be Needed” When You Do, In Fact, Have a Relationship

Let’s pick up the topic from last week and examine another instance where you might encounter the dreaded warning message above.

Let’s start with this simple pivot:

image

Which is a column from the Years table and a column from the Movies table:

image

But now I have a new table:  Animals, which simply lists, for each year, which Animal is specified by the Chinese Zodiac:

image

Hollywood Insiders Know That the Animals of the Chinese Zodiac Are
Incredibly Important to Box Office Revenues

 

Read the rest of this entry »


“Relationship may be needed?” But I already have a relationship! What’s going on?

May 30, 2013

 
image

“Well I’m not quite ready for a relationship right now Lois.”

A confusing error getting you down?

I got an email this morning from a friend who was running into this problem:

PowerPivot says "Relationship may be needed" - but you do have a relationship.  What's going on?

Are you getting this warning in the field list,
even though you DO have a relationship in place?

What gives?  Let’s return to our recent movies example.

To help you diagnose this problem, should you run into it, first let’s get a simple example ready.

Two tables of data in PowerPivot:  Movies, and Years.

Read the rest of this entry »