Lookupvalue function in PowerPivot

[NairbNam]

Hi Guys,

I'm working in PowerPivot 2013 and need some help.

I have a large table of transactional customer data (ordered by transaction date) that includes a number of different fields. Based on my years of data I want to determine if a certain characteristic 'HasCharacteristic' is met by a customer IE if([HasCharacteristic]=1,....,....).

If the characteristic is returned 'True' I want to us a unique 'CustomerID' field to look up the earliest date in this customer's history that this the 'HasCharacteristic' was satisfied as true. If 'False' I will return another date field 'AlternateDate'.

In excel this is a fairly straightforward task but I'm new to powerpivot and not sure how to attain the desired result within a calculated column. Am I asking too much of powerpivot?

Thanks!

 

[scottsen]

No, you are not asking too much of PowerPivot... at all. Likely this will be easier here than traditional excel, and certainly more portable/reusable.

When you say "customer's history" is that coming from a separate table... or is all this work just in 1 table?

As always, it's much easier to help if we can see a bit of sample data, or even better... share the workbook (dropbox/googledrive/whatever).

 

[NairbNam]

Hi Scott,

It's all in a single table in PowerPivot and the columns I am interested in are:

CustID, ReportDate, IsProvisioned, OrderStartDate

So here's what I'm trying to do, if IsProvisioned = 1 then I want to look up the first ReportDate that this is true (IsProvisioned = 1) for based on the CustID, if it's false I want to use the OrderStartDate for this CustID.

Does that help to clarify at all?

If needed I can add a sample to GoogleDrive.

Thanks!

 

[scottsen]

It sounds like all the info required is on the same row of the table? If so, a simple calculated column will do you fine, and is just like you think:

=IF(MyTable[IsProvisioned] = 1, MyTable[ReportDate], MyTable[OrderStartDate])

 

[NairbNam]

Actually, the info that is required is not on the same row, which is my dilemma. Each row represents a single month of customer transactions. What I need to determine is from this data, what was the first month where 'IsProvisioned' = 1 and return that on all rows. I'm not quite sure how to accomplish this in PowerPivot.

 

[scottsen]

Okay, I think this is what you need, just don't... you know cry and/or hurt me

=
IF (
    MyTable[IsProvisioned] = 0,
    MyTable[OrderStartDate],
    CALCULATE (
        MIN ( MyTable[ReportDate] ),
        FILTER (
            ALL ( MyTable ),
            MyTable[IsProvisioned] = 1
                && MyTable[CustID] = EARLIER ( MyTable[CustID] )
        )
    )
)

In psuedo-English:
If IsProvisioned is 0, return the order start date. Otherwise, go find me the min report date (MIN), by looking at the whole table (ALL) not just the current row, but only looking at rows where (FILTER) IsProvisioned=1 and the CustId are the same (EARLIER).

Note I have this as a calculated column which has the draw back of being "static". For this row, the value is never going to change, or respond dynamically to slicers and such. My reading of the problem was... that was fine.

 

[NairbNam]

This looks like it works perfectly, thanks for the help Scott!