Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

LookupValue as a Calculated Measure

Hi all, 

 

I created calculated column where I wrote the following formula. I use the Lookup Value function with Indexes there. this formula works well, but I want to ask if you could write me how to write the same formula but for "Calculated Measures" ? Thank you

 

Weighted ABS Error Statistical Lag 1 last 3M = ('LAG 1 Sales History'[ABS Error Statistical Lag 1]*0.6 + (LOOKUPVALUE(

'LAG 1 Sales History'[ABS Error Statistical Lag 1],

'LAG 1 Sales History'[Index],

'LAG 1 Sales History'[Index] - 1))

*0.25 + (LOOKUPVALUE(

'LAG 1 Sales History'[ABS Error Statistical Lag 1],

'LAG 1 Sales History'[Index],

'LAG 1 Sales History'[Index] - 2))

*0.15)/1

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please refer to my pbix file to see if it helps you.

    Create a measure.

    Measure =
VAR _3 =
    CALCULATE (
        MAX ( 'Table'[ABS Error] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DFU] = SELECTEDVALUE ( 'Table'[DFU] )
                && 'Table'[index]
                    = SELECTEDVALUE ( 'Table'[index] ) - 2
        )
    )
VAR _2 =
    CALCULATE (
        MAX ( 'Table'[ABS Error] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DFU] = SELECTEDVALUE ( 'Table'[DFU] )
                && 'Table'[index]
                    = SELECTEDVALUE ( 'Table'[index] ) - 1
        )
    )
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[ABS Error] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DFU] = SELECTEDVALUE ( 'Table'[DFU] )
                && 'Table'[index] = SELECTEDVALUE ( 'Table'[index] )
        )
    )
VAR _1RE = ( ( _3 * 0.6 ) + ( _2 * 0.25 ) + ( _1 * 0.15 ) ) / 1
RETURN
    IF ( MAX ( 'Table'[index] ) <= 1, BLANK (), _1RE )

     

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please have a try.

    Create a measure.

    Weighted ABS Error Statistical Lag 1 last 3M =
MAX ( 'LAG 1 Sales History'[ABS Error Statistical Lag 1] ) * 0.6
    + (
        LOOKUPVALUE (
            'LAG 1 Sales History'[ABS Error Statistical Lag 1],
            'LAG 1 Sales History'[Index], 'LAG 1 Sales History'[Index] - 1
        ) * 0.25
            + (
                LOOKUPVALUE (
                    'LAG 1 Sales History'[ABS Error Statistical Lag 1],
                    'LAG 1 Sales History'[Index], 'LAG 1 Sales History'[Index] - 2
                )
            ) * 0.15
    ) / 1

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      it is not working, it´s showing this error 

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      A small example in Excel of what we are trying to do in PBi with the help of indexes. 

      Is there any other way to achieve this? But only Calculated Measure, because Calculated Column takes a lot of memory

      Thanks



       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous ,

        Please refer to my pbix file to see if it helps you.

        Create a measure.

        Measure =
VAR _3 =
    CALCULATE (
        MAX ( 'Table'[ABS Error] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DFU] = SELECTEDVALUE ( 'Table'[DFU] )
                && 'Table'[index]
                    = SELECTEDVALUE ( 'Table'[index] ) - 2
        )
    )
VAR _2 =
    CALCULATE (
        MAX ( 'Table'[ABS Error] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DFU] = SELECTEDVALUE ( 'Table'[DFU] )
                && 'Table'[index]
                    = SELECTEDVALUE ( 'Table'[index] ) - 1
        )
    )
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[ABS Error] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DFU] = SELECTEDVALUE ( 'Table'[DFU] )
                && 'Table'[index] = SELECTEDVALUE ( 'Table'[index] )
        )
    )
VAR _1RE = ( ( _3 * 0.6 ) + ( _2 * 0.25 ) + ( _1 * 0.15 ) ) / 1
RETURN
    IF ( MAX ( 'Table'[index] ) <= 1, BLANK (), _1RE )

         

         

        If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

         

        Best Regards
        Community Support Team _ Polly

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