adding / sum lookupvalue

[lattys]

Hello Everyone!

Quick question is there a way to add up values that are coming from a measure that uses lookupvalue? 

Here's the measure: 

lookupvalue = LOOKUPVALUE(Report[SUM_SERVICE_MONTHLY_FEE_HUF];Report[Custom_date]; [TOP2]; Report[LINK_CODE]; [link_select] )

Basically the [link_select] is just the link code, I dunno I just put it there just to be sure. (Link code is a key-like column)

and it uses this measure:

TOP2 = CALCULATE( FIRSTNONBLANK(TOPN(2;CALCULATETABLE(VALUES(Report[Custom_date]);DATESBETWEEN(Report[Custom_date];BLANK();SELECTEDVALUE(Report[Custom_date];LASTDATE(Report[Custom_date]))); GROUPBY(Report; Report[LINK_CODE]));Report[Custom_date]); 0))

[Custom_date] is a date column that I created using 3 date columns. It can contain duplikates.

Basically I need a measure/column whichever fits, to get the previous date data (e.g sum fee, how many distinct link code and other summed products). I wrote sum, but I only need it when the date is agregated to months or groups.

Thank you 🙂

Viki

[v-yulgu-msft]

Hi @lattys,

As I know, it is not able to use a LOOKUPVALUE function in  measure formula. From above measure formulas, I cannot understand what you were trying to achieve, would you please share some sample data and show us your desired result?

Regards,

Yuliana Gu

[Greg_Deckler]

So typically, you use VAR to create a temp table using SUMMARIZE or ADDCOLUMNS and then feed that into a SUMX or something along those lines. See the basic technique here:

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

[lattys]

Thank you 🙂

I have already tried this one, but ones again like this:

sumx = SUMX(SUMMARIZE(Report; Report[LINK_CODE];Report[Custom_date]; "look"; [lookupvalue]);[look])

sumx = SUMX(ADDCOLUMNS(Report; "look"; [lookupvalue]; "link"; Report[LINK_CODE]; "date"; Report[Custom_date]);[look])  

 and also when I tied to make a separate table I get this error:

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