Forum Discussion

bo_afk's avatar
bo_afk
Icon for Post Patron rankPost Patron
6 years ago
Solved

LOOKUPVALUE function in a measure

Hi,

I'm creating a measure using the LOOKUPVALUE function but I'm having some trouble. I'm looking up a value based on a value of another column but it doesnt actually allow me to select a column in the 'search value' part of the formula.

 

Formula = LOOKUPVALUE ( <Result_ColumnName>, <Search_ColumnName>, <Search_Value> )

Expected Measure = LOOKUPVALUE( 'Table'[Units] , 'Table'[Year], 'Table'[RefYear] )

 

If I create this as a column, I am able to select the column as the search value, but I can't seem to do this in a measure. Does anyone know how I can go around this?

Thanks

afk

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, bo_afk 

     

    You may modify the measure as below. 

     

    MeasureUnits = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Result",
    var _refyear=[Ref_Year]
    var _refweek=[Ref_Week]
    return
    CALCULATE(
        SUM('Table'[Units]),
        FILTER(
            ALL('Table'),
            'Table'[Year]=_refyear&&
            'Table'[Week]=_refweek
        )
    )
)
return
SUMX(
    tab,
    [Result]
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

12 Replies

    • bo_afk's avatar
      bo_afk
      Icon for Post Patron rankPost Patron
      6 years ago

      Hi amitchandak, please see below sample data and output. The 'measureunits' is what I'm trying to output using the LOOKUPVALUE function. Hopefully it makes sense?

      Sample Data

      YearWeekUnitsRef_YearRef_Week
      2020151420191
      2020248120191
      2020348720192
      2020447020194
      20191636  
      20192500  
      20193520  
      20194463  
      20195541  

       

      Sample output

      YearWeekUnitsMeasureUnits
      20201514636
      20202481636
      20203487500
      20204470463
      • bo_afk's avatar
        bo_afk
        Icon for Post Patron rankPost Patron
        6 years ago

        Hi amitchandak, do you have any ideas for this so that I can also display the measure total and not just row level?

         

        Thanks

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    6 years ago

    This should give you some Idea.

     

    Measure 3 =
SUMX (
    Sales,
    VAR ProductKey = Sales[ProductKey]
    VAR Price =
        LOOKUPVALUE ( Products[Unit Price], Products[ProductKey], ProductKey )
    RETURN
        Price * Sales[Quantity]
)
    Total Sales =
SUMX ( Sales, Sales[Quantity] * RELATED ( Products[Unit Price] ) )

     

    Basically rewrote the same measure using LOOKUPVALUE

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, bo_afk 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

    Table:

     

    You may create a meaure as below.

    MeasureUnits = 
LOOKUPVALUE(
    'Table'[Units],
    'Table'[Year],
    SELECTEDVALUE('Table'[Ref_Year]),
    'Table'[Week],
    SELECTEDVALUE('Table'[Ref_Week])
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • bo_afk's avatar
      bo_afk
      Icon for Post Patron rankPost Patron
      6 years ago

      Hi amitchandak, thanks for your suggestion. I've applied this to a more complex dataset but get the following error message.

      "Calculation error in measure.....A table of multiple values was supplied where a single value was expected"

       

      I have another column called 'category' so the same year and week number will appear on multiple rows for different categories. Can you advise how I would go about it in this situation?

       

      Thanks again!

      afk

      • v-alq-msft's avatar
        v-alq-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi, bo_afk 

         

        I modified the data as below. The pbix file is attached in the end.

         

         

        You may try the following measure.

        MeasureUnits = 
CALCULATE(
    SUM('Table'[Units]),
    FILTER(
        ALL('Table'),
        'Table'[Year]=SELECTEDVALUE('Table'[Ref_Year])&&
        'Table'[Week]=SELECTEDVALUE('Table'[Ref_Week])
    )
)

         

        Result:

         

        Best Regards

        Allan

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.