LOOKUPVALUE not supported in DirectQuery. Is there a work around??

[MidiGlitch]

Hi:

Am trying to implement an expotential smoothing calc to a line graph and need to access the previous row's data. When trying to add a calculated column LOOKUPVALUE is said to be unsupported, so am looking for an alternative way to get the previous row's data. My table has an index that I can use.

Ideally I want to do something like:

ExpotSmooth = IF(
    Tab[Index] = 1, 0,
        IF(
           Tab[Index] = 2, LOOKUPVALUE(Tab[FPS], Tab[Index], Tab[Index-1]),
                 LOOKUPVALUE(Tab[FPS], Tab[Index], Tab[Index-1]) * 0.1 + LOOKUPVALUE(Tab[ExpotSmooth], Tab[Index], Tab[Index-1]) * 0.9
        )
    )

Thx

[v-caliao-msft]

@MidiGlitch,

Yes, we cannot use this function when connect to database by using directquery mode. To work around this issue, you'd better create such a column in your database. And then connect to this database in Power BI. 

Thank you for your understanding.

Regards,

Charlie Liao