DAX Filter function (Text column) in Power BI

In this Power BI Tutorial, We will discuss how to Filter a Text column from a List using Power BI DAX.

By taking one simple example, I will explain to you how to use Power BI DAX Filter function to understand how to filter a text column in Power BI DAX.

Requirement:

As per my requirement, I have a MarkSheet List of students. This SharePoint List (MarkSheet) has three columns with different data types as:

  • Title (By default column of the List)
  • Subject (Choice Data Type)
  • Marks (Number Data Type)

Here I have a SharePoint list “MarkSheet” with some data like below:

DAX Filter function

Here, my requirement is to filter based on a particular value from the Title column.

The SharePoint Online list contains, the mark sheet of all students in different subjects. But, I want to view the mark sheet of Preeti only in every subject.

This thing also I need to get by using the Power BI Measure or DAX formula.

Power BI Filter a Text Column

To filter a Text column from the list, you need to follow these below steps:

  • Sign in the Power BI Desktop with your Microsoft Account
  • Get the MarkSheet List to your Power BI Desktop
  • Format the data type of Marks as the Whole Number
  • Apply Text Column Filter formula using Power BI Measure
  • Test the Measure(Text Column Filter) by taking any chart from the Visualization

Filter a Text Column using Power BI DAX

Step-1:

First of all, Open your Power BI Desktop and Sign in with your Microsoft account. Get the SharePoint List (Marksheet) from SharePoint Online Site to your Power BI Desktop.

If you want to know how to get the SharePoint List from SharePoint Online Site to Power BI Desktop, then you can refer this link: Get SharePoint List to Power BI Desktop

After doing all these things, You can see your list as a table under the Fields section in Power BI Desktop. Below represents my Marksheet table:

DAX Filter function power bi

Step-2: (Format the data type of Marks as the Whole Number)

Now you have to format the data type of [Marks] column as the Whole Number.

To change the data type of Marks, click on the ‘MarkSheet'[Marks] and go to the Modelling tab and then select the data type as the Whole Number from the Data type section.

If you are interested to know the more details about the Data types in Power BI, then click on this link: Power BI Data type

After formating the [Marks] column in the MarkSheet table, it will appear as shown below:

Power Bi DAX Filter function

Step-3: (Apply Text Column Filter formula using Power BI Measure)

At last, You have to apply the Power BI DAX rule to get the mark details of a particular student in each subject.

For this purpose, You need to create a New measure in the MarkSheet table. If you are unaware to create a Power BI new measure, follow this link: Power BI Measure

Once you have created the new measure, apply the below formula to filter the text column:

Marks Of Preeti = 
VAR __table = FILTER('MarkSheet',[Title] = "Preeti")
RETURN
SUMX(__table,[Marks])

Also, you can refer this Power BI Measure formula from this below screenshot:

power bi filter column by text

Step-4: (Test the measure for Filtering the Text)

After completing all the steps, Don’t forget to test your requirements. For testing purposes, Click on any chart from the Visualizations section. Here, For this example, I was taking a Clustered Bar chart.

Just drag and drop the Measure(Marks Of Preeti) to Fields section of the Clustered Bar Chart.

When you put the measure(Marks Of Preeti) into the fields section, then you can see all the marks of Preeti in each subject as like below screenshot:

power bi filter a text column using dax

In the above screenshot, I have taken the left side table visual for your reference purpose, so that you can understand easily.

As per my requirement, I want to show only the marks of Preeti, So you can view all the marks in every subject by doing these above things.

Also, You may like following the below Power BI tutorials:

Hence in this Power BI Tutorial, By taking this above requirement, We discussed how to Filter a Text column details from a List using Power BI DAX.

>