Power BI Measure If Text [With Examples]

by

In this Power Bi tutorial, we will learn what Power BI IF contains and how to work with the Power BI Measure if the text contains different examples in Power Bi. We will also cover the below headings:

  • Power BI Measure If Text equals then
  • Power BI Measure If Text then Dax
  • Power Query checks if a text contains
  • Power BI checks If the Text blank
  • Power BI checks If the Text is a number
  • Power BI checks If Text exists in another table

Power BI IF contains

The power BI CONTAINS() function returns if values for all referred columns exist then it returns a true value else it returns a false value.

Below is the syntax for the Power Contains Function:

CONTAINS(<table>, <columnName>, <value>[, <columnName>, <value>]…)

In the following, we will see how to use the Contains function in Power BI.

Power BI Measure If Text Contains

Let us see how to find that contains a specified value using the Power Bi Contains function in Power BI.

In this example, we will use the below Stocks table data as a data source and find the text values using the Power Bi Dax functions in Power BI.

Power BI IF contains
Power BI IF contains

Log in to the Power Bi desktop and load data using the get data option. Once the data has been loaded, select the new measure option and use the below formula:

Text Contains = CONTAINS(Stocks, Stocks[Symbol],"AAPL")

Where,

  1. Text Contains = New Measure name
  2. CONTAINS = Function Name
  3. Stocks = Table Name
  4. Symbol = Column Name

Select the table visual from the visualization, drop the Stock name, Symbol, and the created measure value into the columns section as shown below:

Power BI Measure If Text Contains
Power BI Measure If Text Contains

In the below screenshot, you can see if the text value contains the mentioned value it returns the true value else false.

Power BI Measure If Text contains then
Power BI Measure If Text Contains example

This is how to find that contains a specified value using the Power Bi Contains function in Power BI.

Power BI Measure If Text equals then

Here we will see how to create a measure to find the text value using the Power BI If function in Power BI.

In this example, we will use the measure if the text value equals then match else it displays the value mismatch.

Load data using the get data option into the Power Bi desktop. Click on the new measure option and apply the below-mentioned formula:

Text equals = If( SELECTEDVALUE(Stocks[Symbol])="ORCL","match","mismatch")

Where,

  1. Text equals = New Measure name
  2. SELECTEDVALUE= Function Name
  3. Stocks = Table Name
  4. Symbol = Column Name

From the visualization select the table visual, then drag and drop the Stock name, Symbol, and the created measure Text equals value into the columns section as highlighted below:

Power BI Measure If Text equals then
Power BI Measure If Text equals then

The Screenshot below displays the text value if the text equals the mentioned text then matches else it displays the value mismatch.

Example of Power BI Measure If Text equals then
Example of Power BI Measure If Text equals then

This is how to create a measure to find the text value using the Power BI if function in Power BI.

Power BI Measure If Text then Dax

Let us see how to create a calculated column of text value using the Power Bi Dax function in Power BI.

In this example, we will add a new calculated column to find if the text value matches with mentioned value else it displays a blank value in Power Bi.

Open and Load data using the get data option into the Power Bi desktop. Click on the new column option under the modeling tab and apply the below-mentioned formula:

Measure If Text = IF(
	ISERROR(
		SEARCH("A",Stocks[Symbol])
	),
	BLANK(),
	"True"
)
  1. Measure If Text= New Calculated column
  2. SEARCH= Function Name
  3. Stocks = Table Name
  4. Symbol = Existing Column Name

In the below screenshot, you can see that the newly added column displays the result as true when the Symbol column starts with the text value A. Else, it displays the blank value.

Power BI Measure If Text then Dax
Power BI Measure If Text then Dax

This is how to create a calculated column of text value using the Power Bi Dax function in Power BI.

Power Query checks if a text contains

Here we will see how we can check if a text contains a specified value using the Power Query editor in Power BI.

In this example, we will use the Query editor to add a custom column, if the specified text value matches with the text contains column then it returns the true value else false.

To achieve this follow the below-mentioned steps:

Step:1

Load the data into the Power Bi desktop, and under the Home, tab selects the transform data option as shown below: It will automatically redirect to the Power Query editor.

Power BI checks If the Text contains
Power BI checks If the Text contains

Step:2

In the Power Query editor, Add a custom column by selecting Add Column-> Custom column as highlighted below:

power query check if a text contains
power query check if a text contains

Step:3

  • In the custom column pop-up window, enter the new column name and add the below-mentioned formula and click on the ok button.
if(Text.Contains([Symbol],"MSFT")) then "TRUE" else "FALSE"
power query check if a text contains
power query check if a text contains

Step:4

  • In the below screenshot, we can see that the newly added custom column displays the value based on the condition applied.
  • If the Symbol column contains the text MSFT then it returns the true value else False.
  • Click on Close & Apply under the home tab, so that the changes will be reflected on the Power Bi desktop.
= Table.AddColumn(#"Changed Type", "Text Contains", each if(Text.Contains([Symbol],"MSFT")) then "TRUE" else "FALSE")
Example of power query check if a text contains
An example of a power query check if a text contains

This is how to check if a text contains a specified value using the Power Query editor in Power BI.

Power BI checks If the Text Blank

Let us see how to show the text value if the row is empty or blank using the Power Bi Blank function in Power Bi.

In this example, we will see if the rows are empty and then displays the text value as missing data else it displays the values presented in the rows.

Load data using the get data option into the Power Bi desktop. Once the data has been loaded, select the new measure option and use the below-mentioned formula:

Text Blank = if( SELECTEDVALUE(Stocks[Symbol])= BLANK(),"Missing Data",SELECTEDVALUE(Stocks[Symbol]))

Where,

  1. Text Blank = New Measure name
  2. SELECTEDVALUE= Function Name
  3. Stocks = Table Name
  4. Symbol = Column Name

Select the table visual from the visualization, drag and drop the Stock name, Symbol, and the created Text Blank measure value into the columns section as shown below:

Power BI checks If the Text Blank
Power BI checks If the Text Blank

The Screenshot below displays the value as missing data whenever the symbol column has empty or blank row values else it displays the existing row values in it.

Example of Power BI checks If the Text Blank
Example of Power BI checks If the Text Blank

This is how to show the text value if the row is empty or blank using the Power Bi Blank function in Power Bi.

Power BI checks If the Text is a number

Here we will see how to check if the text is a number using the Power Query editor in Power BI.

In this example, we will see the text column is a text data type then it displays the true value if the text column contains the null value which is of number data type then it returns a false value.

  • Load the data into the Power Bi desktop, and Select the Home tab -> to transform data option, It will automatically redirect to the Power Query editor.
  • In the below screenshot, you can see that the symbol column is of the text data type (highlighted in red).
Power BI checks If the Text is a number
Power BI checks If the Text is a number
  • To check the value of the text data type add a custom column and use the below-mentioned formula by selecting select the Add Column-> Custom column in the power query editor.
=if(Value.Is(Value.FromText([Symbol]), type text)) then "TRUE" else "FALSE"
Power BI checks If the Text is a number example
Power BI checks If the Text is a number example
  • In the below screenshot, we can see that the newly added custom column displays the value based on the condition applied.
  • If the Symbol column contains the text data type value then it returns the true value else it returns the False value.
  • Click on Close & Apply under the home tab, so that the changes will be reflected on the Power Bi desktop.
= Table.AddColumn(#"Removed Columns", "Text Datatype", each if(Value.Is(Value.FromText([Symbol]), type text)) then "TRUE" else "FALSE")
Example of Power BI checks If the Text is a number
Example of Power BI checks If the Text is a number

This is how to check if the text is a number using the Power Query editor in Power BI.

Power BI checks If Text exists in another table

Let us see how we can check if text exists in another table in Power BI.

In this example, we will use two tables to check if the text exists in another table returns the true value or else the false value.

Table:1

Power BI IF contains
Power BI IF contains

Table:2

Power BI checks If Text exists in another table
Power BI checks If Text exists in another table

Load the table data into the Power Bi desktop, and make many to one relationship in the model view as shown below:

Power BI checks If Text exists in another table example
Power BI checks If Text exists in another table example

Click on the new column option under the modeling tab and apply the below-mentioned formula:

Flag = IF (Stocks[Purchase Price] IN DISTINCT ( 'Table 2'[Purchase Price]), "TRUE"," FALSE" )

Where,

  1. Flag = New Calculated column
  2. DISTINCT= Function Name
  3. Stocks = Table Name
  4. Purchase Price = Existing Column Name
  5. Table 2 = Another table name

In the below screenshot, you can see that the newly added column displays the result as true when the Symbol column matches the purchase price value. Else, it displays the false value.

Example of Power BI checks If Text exists in another table
Example of Power BI checks If Text exists in another table

This is how to check if text exists in another table in Power BI.

In this Power Bi tutorial, we have learned what Power BI IF contains and how to work with the Power BI Measure if the text contains different examples in Power Bi. We also covered the below headings:

  • Power BI Measure If Text equals then
  • Power BI Measure If Text then Dax
  • Power Query checks if a text contains
  • Power BI checks If the Text blank
  • Power BI checks If the Text is a number
  • Power BI checks If Text exists in another table

You may like the following power bi tutorials:

>