Forum Discussion

Matthew_Theis's avatar
Matthew_Theis
Advocate II
8 years ago
Solved

Problems with LookupValue

Hello Everyone,   Thanks for the help in advance.  I'm trying to utilize LOOKUPVALUE to provide me with the previous weeks' price for any given PartNumber and Distributor.  I don't want to hardcode...
Show and Tell
Tutorial Requests
  • Matthew_Theis's avatar
    Matthew_Theis
    8 years ago

    I was able to come up with a solution to my problem.  Here is the query that I used...

    Previous Price =
    CALCULATE(
    SUM('Octopus FindChipsExport'[Price]),
    FILTER(
    ALLSELECTED('Octopus FindChipsExport'),
    'Octopus FindChipsExport'[DateOfImport] = SELECTEDVALUE('Octopus FindChipsExport'[DateOfImport]) - 7
    && 'Octopus FindChipsExport'[Distributor] = SELECTEDVALUE('Octopus FindChipsExport'[Distributor])
    && 'Octopus FindChipsExport'[PartNumber] = SELECTEDVALUE('Octopus FindChipsExport'[PartNumber])
    )
    )

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Matthew_Theis,

 

>>a table of multiple values was supplied where a single value was expected

This error will appear when your formula return multiple values on one row. Please use calculate function to filter table and summary result.

Preview price =
CALCULATE (
    SUM ( Table[Price] ),
    FILTER (
        ALL ( Table ),
        Table[PartNumber] = EARLIER ( [PartNumber] )
            && Table[Distributor] = EARLIER ( [Distributor] )
            && Table[DateOfImport]
                = EARLIER ( [DateOfImport] ) - 7
    )
)

 

Regards,
Xiaoxin Sheng

  • Matthew_Theis's avatar
    Matthew_Theis
    Advocate II
    8 years ago

     

    Hi Anonymous thanks for the reply.  I tried your DAX and it appears that EARLIER isn't able to identify the previous row context, as can be seen by the error above.  

     

    I did come up with something else that may work, much simplier.  

     

    Prior Price =
    CALCULATE(
               MAX('Octopus FindChipsExport'[Price]),
               'Octopus FindChipsExport'[DateOfImport]-7
    )

    Ideally I would be able to have this return blank for prices of 18/08/02 as there is no data to compare to prior to that date.  Are they downsides to this equation, should we be unable to get the one with EARLIER to work properly?

     

    Thank you!

     

    Matthew

    • Matthew_Theis's avatar
      Matthew_Theis
      Advocate II
      8 years ago

      I was able to come up with a solution to my problem.  Here is the query that I used...

      Previous Price =
      CALCULATE(
      SUM('Octopus FindChipsExport'[Price]),
      FILTER(
      ALLSELECTED('Octopus FindChipsExport'),
      'Octopus FindChipsExport'[DateOfImport] = SELECTEDVALUE('Octopus FindChipsExport'[DateOfImport]) - 7
      && 'Octopus FindChipsExport'[Distributor] = SELECTEDVALUE('Octopus FindChipsExport'[Distributor])
      && 'Octopus FindChipsExport'[PartNumber] = SELECTEDVALUE('Octopus FindChipsExport'[PartNumber])
      )
      )