locked
Lookup does not work correctly in a matrix with drilldown RRS feed

  • Question

  • I am having problems getting the lookup function to work correctly in a matrix with drilldown.  In the example shown below, the values in the DWPSA column are from dataset1 and the the values in the Elita column are from dataset2 which uses the lookup function.

    The values in the 2nd column do not roll up correctly when viewing the sums at the year level, but only when drilling down to the month level.

    Is this a limitation of the lookup function or have I set up something incorrectly?

    Before drilldown:

    After drilldown:


    • Edited by aaks12 Thursday, September 29, 2011 2:01 PM
    Thursday, September 29, 2011 2:00 PM

Answers

  • aaks12,

    I don't think so its a bug,

     

    I believe your Elita field is just picking the picking the Top value.

    If i am correct, you must have used Lookup like this:

    =Lookup(Month.value, Month.value, Fields!Elita.value, "DataSet2")

     

    • Its not aggregating on the basis of Year when Month is in collapsed state.

     

    So, just place a sum()

    = Sum( Lookup(Month.value, Month.value, Fields!Elita.value, "DataSet2") )

     



    Regards,
    Manoj
    *Happy to help
    http://experiencingmsbi.blogspot.com/
    Experiencing Microsoft BI
    • Marked as answer by aaks12 Thursday, September 29, 2011 3:28 PM
    Thursday, September 29, 2011 2:50 PM

All replies

  • aaks12,

    I don't think so its a bug,

     

    I believe your Elita field is just picking the picking the Top value.

    If i am correct, you must have used Lookup like this:

    =Lookup(Month.value, Month.value, Fields!Elita.value, "DataSet2")

     

    • Its not aggregating on the basis of Year when Month is in collapsed state.

     

    So, just place a sum()

    = Sum( Lookup(Month.value, Month.value, Fields!Elita.value, "DataSet2") )

     



    Regards,
    Manoj
    *Happy to help
    http://experiencingmsbi.blogspot.com/
    Experiencing Microsoft BI
    • Marked as answer by aaks12 Thursday, September 29, 2011 3:28 PM
    Thursday, September 29, 2011 2:50 PM
  • Manoj,

    Wrapping the Lookup function inside a Sum worked perfectly.  Thank you!

    Thursday, September 29, 2011 3:28 PM