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.
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:
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
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:
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:
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:
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:
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:
- Power BI Query Group Tutorial
- Get Current Month Sales Report using Power BI Measure
- Get Next Month Sales Report in Power BI
- Export Power BI Reports to Excel
- Microsoft Power BI Alerts
- Access to the resource is forbidden error in Power BI
- Export Power BI reports to PDF
- [Video Tutorial] Create Power BI Dashboard step by step tutorial
- Power BI On-premises Data Gateway
- Power BI Buttons
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.
I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site EnjoySharePoint.com