If there is no match that satisfies all the search values, a BLANK is. Greg_Deckler. Remarks. RELATED Vs LOOKUPVALUE DAX. Super User Mark as New; Bookmark; Subscribe; Mute; Subscribe to RSS Feed; Permalink; Print; Report Inappropriate Content 09-10-2020 11:40 AM. There doesn't´t have to be a relationship between these tables. 4 Match and Get the Value from another table (Vlookup). Escribimos la fórmula. The TOPN function returns entire row (s) instead of a single value. I want to create a Measure to Lookup the "Group Role" where the "People Leader" is TRUE and return the "Name". If the manager ID is among the list of managers returned by the PATH function, the PATHCONTAINS function returns true; otherwise it returns false. Even though this function is commonly used for dates, it can be applied to a column of any data type. I have written this code: C = IF ( isblank ( `Table ONE` [B]), lookupvalue ( `Table TWO` [Value],`Table TWO` [Key], `Table ONE` [A]), `Table ONE` [B]) But for some mysterious reason it doesn't work and Column C becomes exactly the same as Column B. I need the measure to do the following, I've stepped this out so its easier to follow: 1. Remeber we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. datas are collected every months on last day. OUTER JOIN. Construct the LOOKUPVALUE formula, specifying the necessary parameters. RELATED vs. Lookupvalue in Range. Very new to using PowerBI & DAX but wanting to see if it is the better option for some of my reporting. 4. The column argument can be any of the following: A reference to any column. View solution in original post. Step-1: Duplicate your table Order and give name Order_max_date. 2. Hello, I have an assignment where I have to calculate the Sales based upon different currency rates, and I have the base Sales in USD. Please close the Editor window and create a column and then try to use this formula. There’s a bit of a learning curve to get this right using Power BI DAX functions. 1. 1 How to lookup the values of a column in a table, if one of its column matches with the other table. Modified 5 years, 8 months ago. IF (. groupBy_ColumnName. These it can pick. Esta función es muy parecida a VLOOKUP de Excel, y este video es una continuación al video que ya había publicado donde hablaba de RELATED. Do anyone knows how to query this output or replacement of LOOKUPVALUE in DirectQuery. In this video you will learn to use LookupValue function in Power BI. Dans cet exemple, LOOKUPVALUE est utilisé pour rechercher le taux moyen de la devise utilisée pour payer la commande le jour où la commande a été passée : DAX. Column UniqueShiftID in Table1 and UniqueID in Table2 are used for referencing the rows. DO for the sample queries and removing the outdated part. EARLIER ( [ TotalSubcategorySales ]) evaluates to $156,167. Both of them gave me error, can you help with this? powerbi; powerquery; powerbi-desktop; lookup-tables; Share. Ashok Anumula Ashok Anumula. Dear Forum. The lookup functions work by using tables and relationships, like a database. Exchange Rate = LOOKUPVALUE ( 'Currency Rate' [Average Rate], 'Currency Rate' [CurrencyKey], [CurrencyKey], 'Currency Rate' [DateKey], [OrderDateKey] ) K vyhledání průměrné sazby pro správné datum a měnu se vyžaduje datum objednávky i měna. ROW 2 Jason Gould. Asking for help, clarification, or responding to other answers. FactRegister table. The following calculated column defined in the Sales table uses the LOOKUPVALUE function to return channel values from the Sales Order table. 1. Create copy of Table 2. ”. [SalesMan] = "LOOKUPVALUE(Users[User],Users[Login],Username())" but it seems it doesn't work. The goal of the project is to convert an old Excel file used as reporting into a power BI Project. = RELATED ( States [StateName] ) and you will have a column with the corresponding state name. Here is my sample data, I wanna get lookupvalue column with "Y" only when I have release in table t2 with date filter April2020 applied to t2. September 2, 2023 How to Use LOOKUPVALUE DAX function in Power BI for Forecasting In this article, we will explore the LOOKUPVALUE DAX function in Power BI and how it can be used for forecasting. DAX Filter Table To Replace Relationship. The LOOKUPVALUE DAX function is a powerful tool in the world of data analysis and modeling using Power BI. I am trying to do a look up value on a simple table. read more to replicate VLOOKUP in Power BI as well. Make sure to include the relevant columns and data that you want to. How to create a calculated column in table 2 and return the color from table 1 filtering by the latest date? Thank you in advance. DAX; LOOKUPVALUE; Message 1 of 3 2,246 Views 0 Reply. I'm trying to create a measure to lookup value either from Date column or Status column based on the status. 商品名以外の部分が準備できたら、「モデリング」から「列の追加」をしてitem列をつくっていきます。. Sorted by: 0. This function is typically used to return the first value of a column for which the expression is not blank. How to get values from one table with condition on another table in power bi? 0. In plain English, I would like the DAX formula to sum the check amount if the payment type in the Financials table is Investigation. To make the example as simple as possible, here. Return value. DAX A table of multiple values was supplied where a single value was expected. Extract text using the LEFT function. Hi, Im trying to lookup a value from another table with MAX. I dette eksempel bruges LOOKUPVALUE til at søge efter gennemsnitskursen for den valuta, der bruges til at betale for ordren den dag, ordren blev afgivet: DAX. Here is what I would like to see: Item number = "123". There are plenty of ways to do this. Thanks for any help. DAX Nesting Multiple IF statements. See the syntax, parameters, return value, remarks and examples of this function. Exchange Rate = LOOKUPVALUE ( 'Currency Rate' [Average Rate], 'Currency Rate' [CurrencyKey], [CurrencyKey], 'Currency Rate' [DateKey], [OrderDateKey] ) Oikean päivämäärän ja valuutan keskiarvon etsiminen. As you can see, there is a large amount of code duplicated for the two columns. IF ( ISBLANK ( Table1[Column1] ), IF ( LOOKUPVALUE ( Table2[Column], Table2[Column], Table1[Column1] ) = Table1[Column1], "True", "False" ), "Data missing" ) If you want to check is blank. Thanks in Advance!! Regards, Krishna. 45 5. This week, let’s discuss the difference between the RELATED and LOOKUPVALUE functions. e. By understanding the basics, syntax, and advanced techniques of the function, analysts can effectively retrieve the desired data points based on specific conditions. Regards. Step 3: Use the calculated column or measure feature in Power BI's formula builder. A100. If there is no match that satisfies all the search values, a BLANK is returned. In this article, you will learn how to effectively use the Power BI Lookup Value function. A table with all its original columns and the added ones. I have the following formula in resultColumn: ResultColumn = LOOKUPVALUE ( Table1 [Value]; Table1 [Code_t1]; Table2 [Code_t2]) It's working when each Code are unique in Table1. ちなみに、このすべての条件に一致. 1. Earliest_Date_Next_Stage = LOOKUPVALUE(Sheet1[Date];Sheet1[Candidate ID];Sheet1[Candidate. But when the data is DirectQuery it seems like LOOKUPVALUE is not supported. I am trying to populate the Manager ID Column from Employee IDText Column where all the managers' name is also in the employee Name column. Even if the relationship is inactive, it will be used and overrides. Remove duplicates on ID, if eixists. 3. PowerApps -If LookUp() is NULL with external data source - Power APPS BREAKS. search_columnName: The name of an existing column, in the same table as result_columnName or in a related. Example This formula computes the increase or decrease ratio in sales compared to the previous year. Download using URL. As you can see in the above table, RELATED and LOOKUPVALUE have the same result, however, each function has its own syntax and. I just want to VLOOKUP the value to every first of the ID (Num = 1) Thank for your attention. Value type] = "Percentage", <Average expression>, <Sum expression>)The <Search Value> for LOOKUPVALUE needs to be a single value, you're supplying an entire column. Assuming Employee is numeric but Wage is a text field (because of the leading zeroes) and assuming Employee can never be null, but wage can be: =Text. 0. In the old vlookup, u can directly pull the first non blank row in order level status. Contacts Table. This function is not supported for use in DirectQuery mode when used in calculated columns or row-level. Clear all filters which are applied to the specified table. From that string PATHITEM returns the third entry as an integer. So basically, I need to perform a lookupvalue based on these 2 columns. using an IF statement, if 'Order Details' [Currency] ="GBP" then take that the value in the 'Order Details' [OrderItemCostExTax] column. But I don't want the sum value are copying to every single row in TABLE B (Based on ID). I am new to DAX, teaching myself and having trouble with a basic example using LOOKUPVALUE in Power BI Desktop. 1. 03-02-2018 02:45 AM. If specified, all columns in <partitionBy> must come from it or a related table. Share. LOOKUPVALUE returns the value from the Result Column Name for the rows that meet all the criteria specified by Search Column Name and Search value. Chapter 18 discusses LOOKUPVALUE, SELECTEDVALUE, RELATED, and VALUES functions in DAX using Power BI. See the syntax, return values, remarks, and. What I am trying to do is create a measure that finds the maximum Valid_From_Dttm prior to 5/1/2108 for a specific project, where Project_Milestone_Name = Project Mature. Hi Lz: Good to see you in the Excel forum and thanks for presenting an alternative solution using Lookup. Else, alternateResult. You can create a calculated column with the LOOKUPVALUE DAX function in the sales tables, it worked for me. The function can apply one or more. LOOKUPVALUE in Microsoft's online documentation on. From this blog, you got some idea about 3 important filter functions (LOOKUPVALUE, SELECTEDVALUE, RELATED. What i am trying to do, is add a column to this table called [PREVIOUS SHIFT FINAL LOAD] which looks up [Previous Shift Name] and returns the max value of [Dump Time] Example would be rows 4/5/6 where [Shift Name] = 01-10-2020-Night Shift the new Column [PREVIOUS SHIFT FINAL LOAD]. VLOOKUP requires the data to be sorted in ascending order. 1. . Search_Column1 = Name of the column from where you want to. To get the model, see DAX sample model. First, please do follow Best Practices and precede column names with their table's name. Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. By using this function, you can easily extract relevant data for cost analysis purposes. I have the two tables separate and created a measure for each column I need to look up: Value1_Lookup = LOOKUPVALUE ('Key Table' [Score],'Key Table' [Value],SELECTEDVALUE ('Master Data' [value 1])) But I am. e. 09-17-2022 08:30 PM. For each PROJECT NAME, find the TASK NAME called "NETWORK READY" and select the START date. 0. So, avoid using the LOOKUPVALUE DAX function when model relationships could achieve the same result. 1. In general, using the example described in this article, the DAX LOOKUPVALUE function can be interpreted as follows. UPDATE 2023-03-17 : Fixed an incorrect description before example #11. In Table1, columns Crew and Shift work with the LOOKUPVALUE function. 商品名以外の部分が準備できたら、「モデリング」から「列の追加」をしてitem列をつくっていきます。. DAX Studio was designed with a clean, modern user interface that supports both a light and dark mode and provides rich metadata about your data model. Both the RELATED and LOOKUPVALUE functions in DAX work similarly to a LOOKUP function in Excel. Trong bài viết này chúng ta sẽ cùng Học Excel Online tìm hiểu về cách sử dụng hàm LOOKUPVALUE trong Power BI. I am trying to create a formula that uses a lookupvalue function within a measure (Which might not be the best way to solve my problem). I have written this code: C = IF ( isblank ( `Table ONE` [B]), lookupvalue ( `Table TWO` [Value],`Table TWO` [Key], `Table ONE` [A]), `Table ONE` [B]) But for some mysterious reason it doesn't work and Column C becomes exactly the same as Column B. 0. As the link says, RELATED is more efficient. Remove duplicates on ID, if eixists. This function can be used to obtain visual. When the result of a calculation is compared with TRUE, the engine can strip the comparison and return the calculation itself. I need help to enhance my DAX commans below. Neste exemplo, a coluna calculada a seguir, definida na tabela Vendas, usa a função LOOKUPVALUE para retornar os valores de canal da tabela Pedido de Venda. 1. I am trying to create a formula that uses a lookupvalue function within a measure (Which might not be the best way to solve my problem). Solved!09-24-2021 03:16 PM. You can split a complex operation into smaller steps by storing a number, a string, or a table into a variable. 2. DAX. 2. Now we can get the best player in the Country_Table using DAX function LookUpvalue or a combination of Calculate,VALUES and FILTER. if the two items are equal filter will take those items make a virtual table and pass to the calculate and calculate simply gives you the first value of table 2 which is item is equal to. Both the RELATED and LOOKUPVALUE functions in DAX work similarly to a LOOKUP function in Excel. Hi there, I want to be able to show a value from table `Points_MaxValue` based on a Selected Date Value (this part is working, looking at the columns `ValidFrom` and `ValidTo` date) and another Value in a different column. OfferedInBoth = VAR unitCode = Table1 [UnitCode] VAR unitCodeTable = FILTER (Table1, Table1 [UnitCode] = unitCode) VAR containsSession = SWITCH ( TRUE. Exchange Rate = LOOKUPVALUE ( 'Currency Rate' [Average Rate], 'Currency Rate' [CurrencyKey], [CurrencyKey],. Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Identify the column containing the desired values: Determine the column that holds the data you want to retrieve. Get Demo Files herethis video were going to cover how you can use the LOOKUPVALUE DAX function in Power BI to match data bet. Copy Conventions # 1. If you can supply some sample data and what your expected result would be, I can see if I can assist further. is. USERELATIONSHIP uses existing relationships in the model, identifying relationships by their ending point columns. The relationship should be in a way that it returns one value from that table per value in the main table. Any DAX expression that returns a table of data. LOOKUPVALUE('DIM_Locations to Products'[Location Name], 'DIM_Locations to Products'[Location_Id], DIM_Products[Location_Id]). DAX. May be one day PQ team would create something similar as M function. Thanks in Advance!! Regards, Krishna. Value = Sales [QTY] * LOOKUPVALUE (Products. DAX LOOKUPVALUE on text/string. This is my DAX formula that I have in the Studio editor . Power BI Relationships - Can't determine the relationships between two or more fields. In other words, it allows you to. You are using it in Power Query Editor which only accepts M. CALCULATE (FIRSTNONBLANK (contacts [emailaddress1],1),FILTER (contacts,contacts [_parentcustomerid_value]=invoices [_customerid_value] )) Solved: Hi Community I am trying to return an email address in a contacts table asscoiated with the customerID in an Invoices table. You can certainly do this in the Edit Query window in Power Query by adding a custom column. If the value is matched then return that value. This function is different from FIRSTNONBLANK in that the <column> is added to the filter context for the evaluation of <expression>. . Product Category AlternateKey. LOOKUPVALUE can handle that just fine as long as there is only possible value to return. Regards . CALCULATE (FIRSTNONBLANK (contacts [emailaddress1],1),FILTER (contacts,contacts [_parentcustomerid_value]=invoices [_customerid_value] )) Solved: Hi Community I am trying to return an email address in a contacts table asscoiated with the customerID in an. = SUMX( RELATEDTABLE('InternetSales_USD') , [SalesAmount_USD]) The following table shows the results: Product Category Key. A step-by-step guide to using the LOOKUPVALUE function in Power BI. Return a value from a specific row in a table using DAX or PowerQuery in PowerBi. 4. 05-17-2017 10:52 AM. If you create two calculated columns that actually reference each other, then you are generating a circular dependency: 1. name: The name given to the column, enclosed in double quotes. Is it possible to convert the datatpye of ID column in MF table to Text when writing the dax code instead of changing it in Power query. RelatedValue=Lookupvalue (task [neededValue],task [IdProject],affect [IdProject]) LOOKUPVALUE Function (DAX) Regards, Xiaoxin Sheng. I may have values in there which would read Baseballbat only. The table is Users and it has 2 columns, Name and ClientID. (Optional) A table expression from which the output is returned. Currently, if I just use the LOOKUPVALUE function to search for "0445" in the ID column of Table1 and return Value2 it will return two results: [(0445, AAA), (0445, AAA)]. This function allows you to filter a table based on one or more conditions. I am pretty new to DAX/PowerBI. MaxRange = LOOKUPVALUE ( data_table [summary_range], data_table [ageattimeofgift], data_table [MaxAge] ) Use this instead of summary_range in your pivot table. But, is there any option i can use as default value incase there is no matching. Taking the exact same code, and referncing TruckOperatorName in Table2, gives the. The solution basically makes use of a LEFT JOIN in SQL implemented in DAX via the LOOKUPVALUE function as depicted in SQL BI's From SQL to DAX: Joining Tables article. DAX. This is the right way to do this. video is part of DAX Guide, the online guide to all the DAX functions, with li. Syntax for the LOOKUPVALUE function LOOKUPVALUE (Result_ColumnName, Search_ColumnName1, Search_Value1) Here, Result_ColumnName = Name of the column which you want to show as an end result. The following example returns the third tier manager of the current employee; it takes the employee and manager IDs as the input to a PATH function that returns a string with the hierarchy of parents to current employee. RELATED and LOOKUPVALUE are DAX functions that used in a calculated column when you need to reference columns from another table to the current table. Yes I tried it. I have used the above formula in imported databases and no problem. = CALCULATE(SUM(ResellerSales_USD [SalesAmount_USD]), ALLEXCEPT(DateTime,. A100. CHANNEL = LOOKUPVALUE('Sales Order'[Channel],'Sales Order'[SalesOrderLineKey],[SalesOrderLineKey]) The LOOKUPVALUE function in Power BI is a powerful tool that allows you to retrieve the value of a specific column in a table that meets specific criteria. Filter for Manager only. 1 ACCEPTED SOLUTION. DAX Power BI. I dette eksempel bruges LOOKUPVALUE til at søge efter gennemsnitskursen for den valuta, der bruges til at betale for ordren den dag, ordren blev afgivet: DAX. I´ve been used LOOKUPVALUE to compare the distinct parameters But my problem is that only bring when the Date is the same in the two tables and I need the LASTDATE Value found. Learn different advanced techniques to retrieve multiple values from a lookup table in DAX, improving code readability and performance in DAX queries. Best_Player = LOOKUPVALUE ( Players_Table[Player], Players_Table[Country], Country_Table[Country], Players_Table[RANK], 1 ) or What I am doing is seeing if the 'Functional Area' column is a certain value, then go and lookup the table that corresponds to that column, depending on the value. ok. Use the COMBINEVALUES function to create new column in each table. A100. DAX lookupvalue from calculated table. LOOKUPVALUE doesn´t need the other table - it can take values from the table where LOOKUPVALUE formula takes place. I have two data tables like this: Table1: Table2: What I'd like to do is, for the empty values in "Table2[Latest Result]" to find the value in "Table1[Result]" based on the highest number in "Table1[Unix]", so i get the latest update from each ID. Date t1. You could create a calculated column using the DAX code below. One table is an Item ID with the name of a. Anyone have a ny ideas would be appreciated. Here's what it looks like, also with the correct manual calculations displayed below the table for validation: What I need is a measure similar to one of these, but of course these fail: Rev Capture Total:= [Cumulative Revised Revenue]*RELATED (Rev_Capture [Revenue Capture]) Rev Capture Total v2:= [Cumulative Revised Revenue]*LOOKUPVALUE (Rev. In a calculated column of Employees, you can just write. Converting SQL query to DAX in Power Bi to get a single value from table. Improve this answer. A100. Go to Table 1 and merge table 2 using ID as the key/lookup value. DAX Userelationship function. Labels: Labels: Need Help; Message 1 of 3 4,486 Views 0 Reply. In Power Pivot for Excel create variable: MaxStartDate:=MAX (Table1 [StartDate]) 2. CHANNEL = LOOKUPVALUE ( 'Sales Order' [Channel], 'Sales Order' [SalesOrderLineKey], [SalesOrderLineKey] ) ただし、この場合、 Sales Order と Sales. Power BI LOOKUPVALUE with a column of values for the search items? (VLOOKUP. Aggregate values if primary ID is same as related [email protected] how to use the LOOKUPVALUE function in DAX to retrieve values from a table in a simple and efficient way. For example, the following query returns the different categories in the Product table: The table returned by VALUES contains 8 strings. I have a delivery note date that i wish to determine is within my period dates, (From and To) which are in the same table. VAR Key =. The LOOKUPVALUE function retrieves the two values, Campaign and Media. Using lookups to match multiple values in columns in Power BI/DAX. 3. DAX. DAX. DAX Filter Context Row context. VAR SelectedDate = MAX ('Calendar' [Calendar Date]) // Replace 'Calendar' with the actual name of your calendar table. To bring in a column from another table, Merge your lookup table with your main table on the common key. I have two tables: 1. This is mostly a good choice, but it also comes with unexpected consequences. ROW 4 Henry Wong. The following lookupvalue formula works however the output is not quite right i. There is a Many to Many relationship based on the Deptartmetns and Role Titles. Any tips on how I can replicate LOOKUPVALUE but in PowerQuery? I have Table A containing Currency & Date. This is similar to the Vlookup function in Excel. A table with a single column. So I need to return either the first matching record. 88, the current row in the outer loop. Replace "Table1" references with the name of your table and add any session/term values applicable to the SWITCH statements. 06-23-2020 12:01 AM. Any help would be much apprecaited. The column argument can be any of the following: A reference to any column. LOOKUPVALUE function is a Power BI Filter function in DAX, it returns the value for the row that meets all criteria specified by one or more search conditions. Learn how to use the DAX function LOOKUPVALUE to return the value for the row that matches all criteria specified by search. Add a comment | 1 With DAX. As stated by Alexis, this DAX expression returns a table and not a single value like a measure. Read this post . You don't need to use EARLIER function in this case. . ROW 3 Krystle Te Raki. Power BI DAX: String manipulation--> how to search multiple values and return a mapping value. With this function, you can. So please click the thumbs up and accept as solution button. It can be any DAX expression that returns a scalar value. In other words, the function will not return a lookup value if only some of the criteria match. The DAX LOOKUPVALUE function as used in that code does not work. powerbi; dax; powerquery; m; Share. The value of result_column at the row where all pairs of search_column and search_value have a match. This can be done within the GUI interface. In this table I would need to add a column min_value containing the minimum value for each ID in another table Table B : I know I could use SUMMARIZE () on my Table B in order to create a calculated table with the minimum value of each ID, save this Table C and then use LOOKUPVALUE () between my Table A and Table C to get the column I. 3724. answered Jan 17, 2013 at 12:41. Any DAX expression that returns a single scalar value where the expression is to be evaluated multiple times (for each row/context). I've got the code below, but this retursn all rows, instead of the earliest date within that. Former versions of DAX allowed BLANK results from a Boolean expression, whereas the more recent versions (Power BI/Excel 2016/SSAS Tabular 2016) only return TRUE or FALSE from a logical. Message 4. 09-02-2020 05:13 AM. expression: Any DAX expression that returns a scalar expression, evaluated for each row of table. Calculated Column --A Table of Multiple Values was supplied using DAX IN POWERBI - 1. The function LOOKUPVALUE returns error message, if multiple values are found in the search table for one key: A table of multiple values was supplied where a. Returns the value for the row that meets all criteria specified by one or more search conditions. In USERELATIONSHIP, the status of a relationship is not important; that is, whether the relationship is active or not does not affect the usage of the function. A table that holds Quartiles by Different levels of a company (Company Location, Company Grouping, etc all in one column). Assigned to every column in a table, this tag identifies the original column in the data model that the values of a column originated from. 在此示例中，LOOKUPVALUE 用于在订单下单当天搜索用于支付订单的货币的“平均费率”：. The second argument is the column or expression that is searched by COUNTX. I am using LOOKUPVALUE, and it works for SVP, VP, and Manager. These it can pick. It allows you to specify one or more conditions to filter the data and. When <position> is out of the boundary, or zero, or BLANK (), INDEX will return an empty table. If a "costcntr" is blank in the lookupvalue any value will do. Eg. For my understanding you want to look for an empty searchValue. Hi all, I'm trying to do a lookupvalue with a filter but can't seem to get it working. It returns the value for the current row that meat the search criteria. A100. Download a Power BI LOOKUPVALUE Excel template to practice with. My Formula:The sample model does not contain any DAX formulas. In this example, the following calculated column defined in the Sales table uses the LOOKUPVALUE function to return channel values from the Sales Order table. Indeed, if LOOKUPVALUE returns TRUE, then the condition is met. Replace "Table1" references with the name of your table and add any session/term values applicable to the SWITCH statements. DAX. Have a good one. DimUser table. One other way to do this is to use the LOOKUPVALUE functions alternative value field. This week, we are going to look at the function: LOOKUPVALUE. Please find the code in the below location. . In the end I will have one column for Investigation and one column for Settlement, as shown in the Test table image. How to lookup the values of a column in a table, if one of its column matches with the other table. Step 2: Right-click on the Orders table and select the New column option from the menu bar. I like using Lookup function in excel because it is flexible and similar to Lookupvalue dax function. pbix. 0. CHANNEL = LOOKUPVALUE('Sales Order'[Channel],'Sales Order'[SalesOrderLineKey],[SalesOrderLineKey])The LOOKUPVALUE function in Power BI is a powerful tool that allows you to retrieve the value of a specific column in a table that meets specific criteria. I have imported two tables from Excel, People and Counties. In all of our VLOOKUP examples we will use the following dataset. DAX Power BI occurrences of character in string. Exchange Rate = LOOKUPVALUE ( 'Currency Rate' [Average Rate], 'Currency Rate' [CurrencyKey], [CurrencyKey], 'Currency Rate' [DateKey],. filter is an iterative function. If you have following two tables respectively called t1 and t2. 0 on Excel 2010 x64. For example, the code. scopef2 = LOOKUPVALUE('Table'[code],'Table'[Project Name],MAX(actuals[Project Name])) The reason is that references to columns generally need an aggregator around them. cannot be determined. It would be of great help if someone could assist with the dax logic.