Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lattys
Frequent Visitor

adding / sum lookupvalue

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

3 REPLIES 3
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.