By: Siddharth Mehta
Overview
While dealing with large volumes of data using complex criteria, validation of the schema and/or data is often a basic check applied in the expression logic. In this chapter we will briefly look at a list of functions that can be used for validation purposes.
Explanation
Below are some of the frequently used information functions that can be used in DAX expressions.
CONTAINS - Returns true if values for all referenced columns exist, or are contained, in those columns; otherwise, the function returns false. We saw a similar function while working with parent-child functions - PATHCONTAINS. This function can be used to verify data before selecting from the dataset.
ISBLANK - Checks whether a value is blank, and returns TRUE or FALSE. Blanks or NULLs are very easy to miss while validating a formula. One should consider wrapping fields in this function wherever the field design or the expression logic has the scope of blank values.
ISNUMBER / ISTEXT / ISLOGICAL - Checks whether a value is a number or text or boolean respectively, and returns TRUE or FALSE. This function is useful in cases where the data type of the fields used in the expression is unknown or subject to change in the future.
LOOKUPVALUE - Returns the resultant value for the row that meets all search criteria. Below is an example of this function in the AdventureWorks tabular model. Internet Sales table is linked to the Currency table by the Currency Key. Internet Sales table contains the Currency Key, but not the Currency Name which is stored in the Currency table. Using the lookupvalue function, we created a calculated field and added the value of Currency Name from the Currency table based on the Currency Key in the Internet Sales table as shown below.
Additional Information
- Consider reading more about Information functions.
