Filtering Rows in Paginated Report based on Certain Words

[Vishruti]

I want to apply a filter on a column in Power BI Paginated report based on certain words.

E.g. I have following data for the column - Project Names.

If the text in this column contains the words 'test' in lower or uppercase or followed by some letters (eg testing or PKTEST)then I want to remove those rows. 

In the above example, I want to filter out the blue rows.

How can I achieve this in the Filter section of Tablix Properties (as shown below)?

[johnbasha33 · Accepted solution]

@Vishruti 

To achieve this in the Filter section of Tablix Properties in a Power BI Paginated report, you can use an expression-based filter. Here's how you can do it:

1. Select the column you want to filter on (e.g., Project Names) in the Tablix Properties.

2. In the Filter section, click on the "Add" button to add a new filter.

3. Choose the expression-based filter option.

4. Enter the following expression:

```DAX
=NOT(InStr(Fields!ProjectNames.Value, "test") > 0)
```

This expression checks if the "Project Names" column contains the word "test" (case insensitive). If it does, the expression returns False, indicating that the row should be filtered out. If it doesn't contain "test", the expression returns True, indicating that the row should be included.

5. Click "OK" to apply the filter.

This filter expression will remove rows where the "Project Names" column contains the word "test" in any case (lowercase, uppercase, or followed by other letters). Make sure to adjust the field name and the keyword as needed based on your actual data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[johnbasha33 · Accepted solution]

@Vishruti 

To achieve this in the Filter section of Tablix Properties in a Power BI Paginated report, you can use an expression-based filter. Here's how you can do it:

1. Select the column you want to filter on (e.g., Project Names) in the Tablix Properties.

2. In the Filter section, click on the "Add" button to add a new filter.

3. Choose the expression-based filter option.

4. Enter the following expression:

```DAX
=NOT(InStr(Fields!ProjectNames.Value, "test") > 0)
```

This expression checks if the "Project Names" column contains the word "test" (case insensitive). If it does, the expression returns False, indicating that the row should be filtered out. If it doesn't contain "test", the expression returns True, indicating that the row should be included.

5. Click "OK" to apply the filter.

This filter expression will remove rows where the "Project Names" column contains the word "test" in any case (lowercase, uppercase, or followed by other letters). Make sure to adjust the field name and the keyword as needed based on your actual data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Vishruti]

Hi @johnbasha33 

What should be selected in Operator, Value and what should be the expression type? Should it be text/boolean/integer?

[johnbasha33 · Accepted solution]

@Vishruti 

In the Filter section of Tablix Properties in a Power BI Paginated report, when using an expression-based filter, you'll need to specify the Operator, Value, and Expression Type as follows:

1. Operator: For the expression `=NOT(InStr(Fields!ProjectNames.Value, "test") > 0)`, the Operator should be set to "Equal" or "Not equal" depending on your specific requirement. Since the expression returns True for rows that should be included and False for rows that should be filtered out, you can choose either "Equal" if you want to keep rows where the expression evaluates to True, or "Not equal" if you want to filter out rows where the expression evaluates to False.

2. Value: Since you're using an expression-based filter, you don't need to specify a specific value here. Instead, the expression itself determines whether a row should be included or filtered out based on the criteria defined in the expression.

3. Expression Type: The expression type should be set to "Boolean" since the expression `=NOT(InStr(Fields!ProjectNames.Value, "test") > 0)` evaluates to a Boolean value (True or False).

So, in summary:
- Operator: Choose "Equal" or "Not equal" based on your requirement.
- Value: Leave it blank since the expression itself determines inclusion or filtering.
- Expression Type: Set it to "Boolean".

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Vishruti]

Thank you @johnbasha33 

your solution worked.

Similarly, can you also guide me on the formula if there are blank project names invthe column and I want to remove those rows?

[johnbasha33]

@Vishruti  here is the expression to remove blanks. 
=NOT(IsNothing(Fields!ProjectNames.Value) OR Fields!ProjectNames.Value = "")