Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
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
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
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |