Filters in Power BI are crucial because they enhance data precision, improve performance, and provide an interactive, customizable user experience.
They ensure that reports are relevant, user-friendly, and tailored to specific business needs, making data analysis more efficient and actionable. Whether you are creating detailed drill-down reports or high-level executive dashboards, filters are an essential tool for deriving meaningful insights.
In Power BI, there are several ways to filter data that contains specific text. You can create a flag in Power Query and use that flag column for filtering on your report page or use the filters pane with advanced filtering options, such as the “Contains” filter.
Among these methods, using the advanced filtering options with the “Contains” filter is often the easiest. However, it’s important to verify that it functions as expected. Let’s take a closer look at how it works and confirm its effectiveness.
Starting with a sample dataset, let’s say you have a data source that looks like this:
Let’s break down the data. We have three columns: ID, Description, and Values. While this example uses a small sample for clarity, the actual dataset will be much larger. This will help us explore filtering options effectively, especially when dealing with extensive data.
Requirement:
To create visuals based on the Description column with the specified filters, here’s how you can set them up in Power BI:
- Visual for Data Containing “DEL”:
- Visual for Data Containing “*DEL*”:
- Visual for Data Containing “#DEL#”:
This setup will allow users to see how the data is filtered based on their specific needs related to the Description column.
Let’s start creating the visuals and validating the data.
- Creation of Table Visual for Data Containing “DEL”:
Once you’ve created a table visually and added the Description and Values columns with “DEL” as a filter in the Description column, you will get the following result:
Visual Level Filter:
As you can see, we have applied a filter to display all records that contain the word ‘DEL.’ As a result, we are now getting the following values. Let’s compare the data to verify if we’re getting the correct results by applying the same filter in Power Query.
Power Query Validation:
We have filtered the Description column using the “Contains” filter with “DEL” in Power Query as shown below
This is the result we obtained after applying the filter condition.
Comparison of Power Query & Power BI Visual results:
- Power Bi Visual Output
- Power Query Output
Both methods yielded the same output as expected. Now, let’s move on to the next Scenario!
2. Creation of Table Visual for Data Containing “*DEL*”:
Once you’ve created a table visually and added the Description and Values columns with “*DEL*” as a filter in the Description column, you will get the following result:
Visual level filter:
We applied a filter for “*DEL*”, but records like “UPDEL”,”APDEL”,”UP#DEL#” and “AP#DEL#” are included in Powe bi Visuals. It means it is not identifying the asterisk symbols and searching for text that contains “DEL” and giving us the results which is wrong.
Let’s Cross-check the output in Power Query also.
Power Query Validation:
We have filtered the Description column using the “Contains” filter with “*DEL*” in Power Query as shown below
This is the result we obtained after applying the filter condition in Power Query.
Comparison of Power Query & Power BI visual results:
Power BI Visual Output and the Power Query Output methods produced different outputs. In the Power BI visual, the filter “*DEL*” does not differentiate based on the case and includes entries like “DEL” meaning it does not recognize the asterisk symbols but in the Power query, we are getting the expected results.
Let’s check for the next Scenario whether we are getting proper data for different symbols.
Creation of Table Visual for Data Containing “#DEL#”:
Once you’ve created a table visually and added the Description and Values columns with “#DEL#” as a filter in the Description column, you will get the following result:
Visual level Filter:
We applied a filter for “#DEL#” and are getting the expected data. Let’s cross-check the output in Power Query as well to ensure consistency.
Power Query Validation:
We have filtered the Description column using the “Contains” filter with #DEL# in Power Query as shown below
This is the result we obtained after applying the filter condition.
Comparison of Power Query & Power BI visual results:
Both methods yielded the same output as expected. So, there is no issue with the Hash symbol.
I have cross-verified a few other symbols like !, @, $, %, + & _. Except for asterisks (*), we are getting the expected output for all the others.
Solution:
Now, let’s discuss a solution to achieve the expected output. The best approach is to create a flag column in Power Query and then use that flag column in the filter pane. This allows for more precise filtering and helps ensure that only the desired records are included in your visuals.
I have created a conditional column named Flag as shown below. Make sure to prioritize values with symbols first, and then handle the normal text entries. This approach will help ensure accurate filtering.
Table data after adding the Flag column:
Let’s add the flag column in the filter pane and see if we achieve the expected results.
As we can see from the above snap for “*DEL*”, it has Zero in the flag column and applied the same in the filter pane as shown below.
Now we have obtained the expected results, and this data also matches what we see in Power Query.
Conclusion:
Power BI does not recognize the asterisk (*) symbol in the ‘Advanced Filtering’ Contains option. However, other symbols work as expected. To get the correct results, we recommend using the solution mentioned above.
For More Details, Diggibyte Technologies Pvt Ltd has all the experts you need. Contact us Today to embed intelligence into your organization.
Author: Sasidhar Cherukuru