Data Warehouses: What Do I Know? Well, I'm no expert. I learned Power Pivot and Power BI through Excel. What I know is from helping to build a SQL data warehouse. That is, I've worked with a data warehouse pro.…
There are people (at least I assume they exist) that plan out the words flying out of their mouths before they actually start speaking. Those people lack imagination and an appreciation for the true art of throwing caution to the…
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
, 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
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…
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:
The Sales Table has TWO Relationships to Calendar – One is Based on OrderDate (Active), and the Other is Based on ShipDate (Inactive – Dashed Line)
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.
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:
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.
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:
Which is a column from the Years table and a column from the Movies table:
But now I have a new table: Animals, which simply lists, for each year, which Animal is specified by the Chinese Zodiac:
Hollywood Insiders Know That the Animals of the Chinese Zodiac Are
Incredibly Important to Box Office Revenues
“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:
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.