+ Reply to Thread
Results 1 to 21 of 21

Powerpivot Lookup Function

Hybrid View

  1. #1
    Registered User
    Join Date
    05-20-2016
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    23

    Powerpivot Lookup Function

    I am trying to write a lookup DAX function in powerpivot to return the audit score on the latest audit date. (see below):
    Supplier Audit Date Score Latest Audit Date
    ABC 1/1/2012 2 5/24/2016
    ABC 4/4/2014 3 5/24/2016
    DEF 1/2/2015 1 1/2/2015
    GHI 5/5/2016 2 5/5/2016
    JKL 5/6/2015 1 5/6/2015
    MNO 2/3/2016 1 2/3/2016
    PQR 4/3/2016 1 4/3/2016
    STU 7/11/2013 1 2/3/2016
    VWX 8/21/2012 1 8/21/2012
    YZ 9/10/2011 3 9/10/2011
    STU 2/3/2016 5 2/3/2016
    ABC 5/24/2016 1 5/24/2016
    I cannot figure out this formula. For example, since the latest date for the audit on ABC supplier is 5/24/2016 I need it to tell me that the audit score for supplier ABC is 1. This will eventually be turned into a count of how many suppliers are in each risk class based on their LATEST score so I need it to only have 1 line per supplier, not showing 1 next to everytime ABC appears. Any help please? Thank you!

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Powerpivot Lookup Function

    Will the last audit always be listed lower in the table than previous audits?

    If so, try the formula below in E1 and fill down:

    =IF(INDEX($B$1:$B$12,LOOKUP(2,1/($A$1:$A$12=$A1),ROW($A$1:$A$12)))=$B1,INDEX($C$1:$C$12,LOOKUP(2,1/($A$1:$A$12=$A1),ROW($A$1:$A$12))),"")
    Last edited by CAntosh; 05-24-2016 at 04:15 PM.

  3. #3
    Registered User
    Join Date
    05-20-2016
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    23

    Re: Powerpivot Lookup Function

    Yes it will. They do audits on different years so the data will be entered something like this:
    Supplier Audit Date
    ABC 1/1/2013
    DEF 2/6/2016
    ABC 1/2/2015

  4. #4
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Powerpivot Lookup Function

    The formula in post #2 doesn't depend on column D at all, so it might work for you. It's designed as though supplier is in column A, audit date is in B, and score is in C. Let me know if does what you're looking for.

  5. #5
    Registered User
    Join Date
    05-20-2016
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    23

    Re: Powerpivot Lookup Function

    Thank you. The data is listed as you mentioned in A/b/c however when I tried the formula it gave me a syntax error. Is this maybe because powerpivot does not have row labels?

  6. #6
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Powerpivot Lookup Function

    That could be it. I have to confess to limited PowerPivot usage. Let me toy with it some more...

  7. #7
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Powerpivot Lookup Function

    Does DAX allow array formulas? The formula below works in Excel, but needs to be entered as an array formula...

    =IF($B1=MAX(IF($A$1:$A$13=$A1,$B$1:$B$13)),$C1,"")

  8. #8
    Registered User
    Join Date
    05-20-2016
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    23

    Re: Powerpivot Lookup Function

    I think powerpivot does allow array formulas however I tried this both in excel and powerpivot and it comes back with blank cells even thought it shows formulas in those cells. Do you happen to know why this could be? I'm open to trying an excel only formula I was just using powerpivot because it seemed like it automated things more but not so sure now that I am playing around with it.

  9. #9
    Registered User
    Join Date
    05-20-2016
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    23

    Re: Powerpivot Lookup Function

    Might help if I ask this as well: With the first row as column headers does that change the formula?

  10. #10
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Powerpivot Lookup Function

    Headers would shift the ranges down one. Take a look at the attachment. I came up with a new formula in column E (non-array) and my old array formula is in G. I also added another row of data while experimenting that you can ignore. See if either formula works for you...
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-20-2016
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    23

    Re: Powerpivot Lookup Function

    Thank you SO much cantosh! this is a solution! I really appreciate your help. However, I do have one question. How do I modify the formula so that when eventually I have 600 rows it counts all of them and not just a2:a14?

  12. #12
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Powerpivot Lookup Function

    Glad to help! Just change A2:A14 to A2:A600 and change all of the other relevant "14"s to "600"s and you should be fine.

  13. #13
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Powerpivot Lookup Function

    @cantosh
    You could modify your array formula to this =IF($B2=MAX(INDEX(($A$2:$A$600=$A2)*($B$2:$B$600),0)),$C2,"") that doesn't require CSE.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  14. #14
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Powerpivot Lookup Function

    Quote Originally Posted by AlKey View Post
    @cantosh
    You could modify your array formula to this =IF($B2=MAX(INDEX(($A$2:$A$600=$A2)*($B$2:$B$600),0)),$C2,"") that doesn't require CSE.
    Another good solution! The non-array version I ended up including in my attachment above was:

    =IF($B2=LOOKUP(2,1/($A$2:$A$14=$A2),$B$2:$B$14),LOOKUP(2,1/($A$2:$A$14=$A2),$C$2:$C$14),"")

  15. #15
    Registered User
    Join Date
    05-20-2016
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    23

    Re: Powerpivot Lookup Function

    Thanks! This is helpful not requiring CSE. Do you happen to know if I have a few blank rows within the data why the formula won't work? Is there a way around this?

  16. #16
    Registered User
    Join Date
    05-20-2016
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    23

    Re: Powerpivot Lookup Function

    Thanks! one more level of complexity I completely forgot about when I was using my dummy data. I will actually have another column before the 1st column that is called "supplier parent company" because sometimes they do an audit on only the parent company and not the child companies so the new "2nd" column will be blank. Is there a way to modify the formula so that is "supplier name" is blank it checks "supplier parent company" in column A instead? Thank you again and sorry to keep adding all these complex questions!

  17. #17
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Powerpivot Lookup Function

    Can you clarify the new twist? Supplier Name is now in column B and supplier parent company is in column A, correct? The parent company's audit would apply to the children, as well, and should show up as the child's most recent audit?

  18. #18
    Registered User
    Join Date
    05-20-2016
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    23

    Re: Powerpivot Lookup Function

    Nevermind! That seems too complicated and I am just going to specify our users give the parent and child company the same audit score for the puroposes of what I am doing. Thank you very much for the solution!

  19. #19
    Registered User
    Join Date
    05-20-2016
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    23

    Re: Powerpivot Lookup Function

    Sorry I know this is super confusing. Supplier Parent Company=Column A Supplier Child COmpany=Column B then for example a parent company gets a score but their child company doesnt.
    Parent Child Score
    ABC 3
    DEF GHI 4

    Is there a way to modify the formula so that if there is nothing in column B it recognizes column A instead?

  20. #20
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Powerpivot Lookup Function

    My brain appears to be pudding this morning. Is the following interpretation correct?

    A parent might have multiple children. The audits for those children should be considered separate and distinct from one another, so:
    Parent Kid Score
    DEF GHI 4
    DEF JKL 5
    ...would be considered two separate businesses. The formula would show both the 4 and the 5 rather than erasing the 4 in favor of the 5. Correct?

  21. #21
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Powerpivot Lookup Function

    I'm glad you were able to figure out the parent/child dilemma. I had to disappear to do actual work, and I was pleased to see that the problem had resolved itself while I was gone. To avoid spitting back errors for blank rows, just wrap the formula in an IFERROR function, so:


    =IFERROR(IF($B2=LOOKUP(2,1/($A$2:$A$600=$A2),$B$2:$B$600),LOOKUP(2,1/($A$2:$A$600=$A2),$C$2:$C$600),"") ,"")

    or

    =IFERROR(IF($B2=MAX(INDEX(($A$2:$A$600=$A2)*($B$2:$B$600),0)),$C2,""),"")

    depending on which non-array formula you went with.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. implicit vs. explicit function in powerpivot, example
    By stephme55 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-12-2016, 05:39 PM
  2. [SOLVED] implicit vs. explicit in powerpivot and the calculate function
    By stephme55 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-05-2016, 03:28 PM
  3. implicit vs. explicit function in powerpivot
    By stephme55 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-29-2016, 06:41 PM
  4. Replies: 4
    Last Post: 07-23-2015, 03:35 PM
  5. Replies: 2
    Last Post: 06-02-2014, 09:34 AM
  6. Replies: 0
    Last Post: 11-20-2012, 05:34 AM
  7. Need help with powerpivot if function
    By buckle2600 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-12-2012, 05:53 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1