LOOKUPVALUE is a function in Microsoft Power BI. It allows you to search for a specific value within the columns of a data table. Find out everything you need to know about this DAX function, and how to master Power BI with our training course.
To make sense of the data they collect, companies around the world are using Power BI. This Microsoft software combines Data Visualisation and Business Intelligence tools, while remaining accessible thanks to its simple, intuitive interface.
It takes just a few minutes to start creating attractive dashboards and reports. What’s more, DAX functions can be used to perform a wide variety of operations on data sets.
One of the most commonly used DAX filter functions is LOOKUPVALUE, which allows you to look up a value in a table column and return a single value.
💡Related articles:
What is Power BI?
Power BI is a Busienss Intelligence and DataViz platform. It collects data from a variety of sources and transforms it into interactive reports and dashboards.
The software is available as a desktop version, mobile application and SaaS cloud service. Numerous connectors enable integration with third-party software and data sources.
DAX analytical functions are predefined codes used to perform data analysis operations. There are several hundred of them.
With Power BI, Microsoft does not skimp on data security. Numerous functions such as sensitivity labelling, end-to-end encryption and real-time access monitoring protect sensitive information.
What is DAX?
DAX or Data Analysis Expressions is a formula expression language used by Microsoft Power BI, Analysis Services and Power Pivot on Excel.
DAX formulas are used to perform advanced calculations and queries on data in tables and columns in tabular data models. For example, you can add a new column to an existing table and let the DAX formula define its values.
DAX functions require input for parameters and then return a value when executed. There are a wide variety of DAX functions.
What is the LOOKUPVALUE function?
LOOKUPVALUE is one of the most widely used DAX filter functions. It is used in particular by DAX developers with Excel expertise.
Based on a set of values for each column in the table, the LOOKUPVALUE function scans the table looking for a specific value for the column.
The syntax of LOOKUPVALUE is as follows: “LOOKUPVALUE( <result column>, <search column>, <search value>, [<search columnname>, <search value> [, …] ],[,<alternate result>])”
This syntax contains four parameters: ‘Result_ColumnName’ corresponds to the column containing the desired value, ‘Search_ColumnName’ is the column containing the search_value, ‘Search-Value’ is the value searched for in the search-column, and ‘Alternate_Result’ is the value returned when there is no value or more than one value in the specified column.
The LOOKUPVALUE function works best when only one value is required. In the case of multiple values, it will display an error or the default result configured as the Alternative Result argument. This argument will also be returned if the search value is not specified in the search_column.
LOOKUPVALUE vs RELATED
It often happens that the LOOKUPVALUE function in the code indicates a missing relationship, or a need to refactor the model for greater efficiency.
The reason is that this function does not use the storage engine in the most efficient way. It requires the ‘CALCULATE’ function to move the filter.
In certain situations, using the ‘Related’ function can offer better performance with a shorter execution time. It speeds up the query and encourages better use of the storage engine.
However, LOOKUPVALUE is better suited to creating calculated columns in an array that take the values of the columns in the array. This function is also preferable when the conditions are more complex and based on multiple columns.
How do I take a Power BI training?
The LOOKUPVALUE function is just one of the many very useful DAX functions in Power BI. There are several hundred functions that can be used in different situations.
To learn how to use Power BI, you can choose DataScientest training. Our certified training course enables any beginner to acquire complete mastery of the platform in just 5 days!
Three different formats are available. The “Beginner” course teaches you how to analyse data and design dashboards using tools such as DAX or Power Query. It lasts 24 hours over three days.
The “Advanced” course will enable you to discover all the secrets of Power BI. You’ll learn to master each of the platform’s services in depth, and discover the concepts of DataFlows and Incremental Refresh in particular. This course takes 14 hours spread over two days.
Finally, the Complete Mastery course combines the ‘Beginner’ and ‘Advanced’ programmes. Over the course of 38 hours spread over five days, you will be introduced to the fundamentals of Power BI before tackling the more advanced concepts.
As a Microsoft Learning Partner, DataScientest is authorised to prepare its learners to sit the PL-300 certification exam, leading to the status of “Microsoft Power BI Data Analyst Associate”.
DataScientest is recognised by the government and is eligible for funding options. Our training is entirely distance learning. Don’t wait any longer, and discover the Power BI certification course!
You now know everything about the LOOKUPVALUE function. For more information on this subject, read our complete dossier on Power BI and our dossier on the DAX language.
