VLOOKUP better than Relationships? Hell must have frozen over

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

Protecting time intelligence expressions against non-consecutive range of days

By Dany Hoter

(Download file here to follow along).

Time intelligence functions are some of the most important functions in DAX.

Being able to compare values between current period and the same period last year is a very common request and one that is a real challenge using native Excel and can easily achieved with DAX.

A typical calculated field might look like:

Sales Last Year:=CALCULATE(
  [Sales]  ,SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey])
)

In quite a few cases once you start using such expressions you will see this annoying error message:

image

The reason is that the time intelligence functions like the one used here SAMEPERIODLASTYEAR require 100% completely consecutive dates in the filter context, with no holes.

Read the Rest

Creating a Histogram with a User Defined Number of Buckets

By Dany Hoter

Intro by Avi: I have often been asked, about ways to provide an “input” to the Power Pivot model from Excel. Disconnected Slicers are a popular way to do this. But with Excel and Power Pivot, there is always more than one way to accomplish a task. Dany shows us how, while making histograms easier to use. He uses a filter dropdown, which even works with Excel Online – inside a browser! Here is the end result, read on to learn how and download file.
image

Take it away Dany…

Introduction

Creating a histogram in Excel based on Power Pivot is not as easy as it should be.

The method I use is no different from what others have already blogged and wrote about. There is even a solution that calculates the number of bins in a histogram with a formula that is based on the total number of cases.

My take on the problem was to let the user choose in run time what is the interval between each bin as a percentage and to show the number of bins accordingly.

Business Case

The model contains data about a service that is in its infancy and so the users experience a relatively high number of errors.

The managers responsible for the service posed the following request:

“We want to see a histogram of the sessions showing how many users have experienced no errors in all the sessions they initiated, how many experienced errors in 10% of the sessions, 20% of the sessions … all the way to these poor users who saw nothing but errors in 100% of the sessions (Told you it is in early stage…)

Solution

Read the Rest

Adding tables to a Power Pivot model from VBA (in Excel 2013)

Guest Post by Dany Hoter

After I published a post about manipulating relationships, Rob suggested that I take a step back and cover the entire scope of what’s possible with the object model.

Can you build a model from scratch? Can you add a new table to an existing table? Can you add calculated columns? What about calculated measures? , Can you change a connection for an existing table in the model?

The short answer to these questions is Yes, Yes, No, No, Yes

The longer version is the rest of this post. Everything in this post is NOT possible in Excel 2010 – this stuff works in 2013 only.

The object model consists of the following elements:

clip_image001

The only property that I found useful in this list is ModelRelationships collection which I used extensively in the previous post.

The ModelTables collection looks promising as it contains ModelTableColumns and could be the way to introduce new tables, new columns or even measures into the model. Unfortunately all these collections are read-only and cannot be used for adding to the model.

So how is still possible to add new tables or even to start a model from scratch?

It all has to do with the method add2 of the Connections collection.

Read the Rest

Manipulating Relationships in VBA (in 2013)

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