lookup value

3 Topics

Get values from a temporary table variable
Below is a piece of the fact table 'fTrans' that provides context to my question. Ticker Date Sale date related to respective purchase Days Transaction Shares Price Purchase total ABEV3 01/Mar/2021 04/Jun/2021 95 Purchase 77 $12.93 $995.61 ABEV3 04/May/2021 04/Jun/2021 31 Purchase 73 $13.60 $992.80 ABEV3 04/Jun/2021 Sale 150 $17.91 ABEV3 28/Jun/2021 29/Oct/2021 123 Purchase 62 $16.01 $992.62 ABEV3 29/Oct/2021 Sale 62 $15.62 ALSO3 07/Jan/2021 03/Feb/2021 27 Purchase 39 $25.12 $979.68 ALSO3 03/Feb/2021 Sale 39 $27.20 ALSO3 29/Jun/2021 30/Mar/2022 274 Purchase 36 $27.53 $991.08 ALSO3 07/Jan/2022 30/Mar/2022 82 Purchase 56 $17.85 $999.60 ALSO3 30/Mar/2022 Sale 92 $22.61 APER3 18/Nov/2021 22/Dec/2021 34 Purchase 33 $29.88 $986.04 APER3 22/Dec/2021 Sale 33 $33.40 APER3 14/Jun/2022 11/Aug/2022 58 Purchase 34 $29.14 $990.76 APER3 04/Aug/2022 11/Aug/2022 7 Purchase 42 $23.57 $989.94 APER3 11/Aug/2022 Sale 76 $30.33 As you can see for all rows whose [Transaction] column is a "Sale" the [Days] column has empty values. The reason being that there are cases where a "Sale" transaction relates to more than one previous "Purchase" transaction, therefore a weighted average of [Days] needs to be calculated for those "Sale" rows. So, my goal is to write up a measure that will provide a weighted average of [Days] for every [Transaction] "Sale" rows that are currently sitting empty, and in order to get there I started with this: EVALUATE VAR Supp_Tbl = ADDCOLUMNS( SUMMARIZE( fTrans, fTrans[Ticker], fTrans[Date], fTrans[Sale date related to respective purchase], fTrans[Transaction], "@Days", SUMX( fTrans, IF( fTrans[Transaction] = "Sale", BLANK(), INT( fTrans[Sale date related to respective purchase] - fTrans[Date] ) ) ), "@PurchTotal", [Purchase total] ), "@WghtProdDaysPurch", [@Days] * [@PurchTotal] ) VAR Days_Tbl = ADDCOLUMNS( GROUPBY( Supp_Tbl, fTrans[Ticker], fTrans[Sale date related to respective purchase], "@SumPurchTotal", SUMX( CURRENTGROUP(), [@PurchTotal] ), "@SumWghtProdDaysPurch", SUMX( CURRENTGROUP(), [@WghtProdDaysPurch] ) ), "@WghtdAvgDays", INT( DIVIDE( [@SumWghtProdDaysPurch], [@SumPurchTotal] ) ) ) RETURN Days_Tbl And it returns the following temporary table VAR "Days_Tbl" whose highlighted [@WghtdAvgDays] provides said weighted average of [Days] that I need for those "Sale" transaction rows: Ticker Sale date related to respective purchase @SumPurchTotal @SumWghtProdDaysPurch @WghtdAvgDays ABEV3 ABEV3 04/Jun/2021 1,988.41 125,359.75 63 ABEV3 29/Out/2021 992.62 122,092.26 123 ALSO3 ALSO3 03/Feb/2021 979.68 26,451.36 27 ALSO3 30/Mar/2022 1,990.68 353,523.12 177 APER3 APER3 22/Dec/2021 986.04 33,525.36 34 APER3 11/Aug/2022 1,980.7 64,393.66 32 Now all I need is to get those values from [@WghtdAvgDays] under VAR "Days_Tbl" whenever [Sale date related to respective purchase] matches fTrans[Date] under each [Ticker]. And for that I first tried by writing the following code: LOOKUPVALUE( [@WghtdAvgDays], fTrans[Sale date related to respective purchase], MAXX( FILTER( fTrans, fTrans[Ticker] = EARLIER( fTrans[Ticker] ) && fTrans[Date] = EARLIER( fTrans[Date] ) && fTrans[Transaction] = "Sale" ), fTrans[Date] ) ) But it right off the bat presents two issues: The column 'fTrans[Sale date related to respective purchase]' either doesn't exist or doesn't have a relationship to any table available in the current context (message given by PBI when committing the measure code). [@WghtdAvgDays] can't be used as the first argument of LOOKUPVALUE as it expects a fully qualified column (message provided by DAX Studio while attempting to troubleshoot the code). How am I supposed to get the values from the [@WghtdAvgDays] column under the temporary table VAR "Days_Tbl" then? Thanks in advance... P.S.: I also tried the route of creating a third temp table with SUMMARIZECOLUMNS but it ends up aggregating the overall total of [Days] for all rows: FILTER( SUMMARIZECOLUMNS( fTrans[Ticker], fTrans[Date], fTrans[Transaction], "@Days", CALCULATE( SUMX( Days_Tbl, [@WghtdAvgDays] ), fTrans[Sale date related to respective purchase] = fTrans[Date] ) ), fTrans[Transaction] = "Sale" ) Ticker Date Transaction Days ABEV3 04/Jun/2021 Sale 30,080 ABEV3 29/Out/2021 Sale 30,080 ALSO3 03/Feb/2021 Sale 30,080 ALSO3 30/Mar/2022 Sale 30,080 APER3 22/Dec/2021 Sale 30,080 APER3 11/Aug/2022 Sale 30,080
Solved
leolapa_br
2 years ago Place DAX Commands and Tips
4.9KViews
0likes
13Comments
LOOKVALUE NEAREST : Nearest value based on other columns values
Hi, i nees your help. I have 2 tables, the fist is the tableA: kardex of amount of a credit line (profiles of credit card limits): the second its tableB: de average purschase by customer. i need the nearest value beetween purchases cust. & profiles, in order to have a credit limit by cust (nearest up). thank u.
Solved
CESARPULIDOGDL
3 years ago Place DAX Commands and Tips
985Views
0likes
2Comments
Dax LOOKUPVALUE for duplicates values and different length values
Hi I need help to enhance my DAX commans below. Below the Dax lookup command works initally what i need but there was changes to the values. O 1) of the changes was with duplicate values. When i run the dax lookupvalue command it gives an error of - A table of multiple values was supplied where a single value was expected 2) The second change was that the current Dax command was loking at the length which was all 7 in length. Now the part number could be 4 to 14 charters long. All suggestion for both or 1 of the items is helpful
Solved
Anonymous
4 years ago Place DAX Commands and Tips
9.4KViews
0likes
3Comments