How to extract info from every row based on Specific text in a column?

[Fahmie]

As per the subject above, I want to extract all the rows that meet the criteria on a certain column. For example below, I want to extract all the row when the column "STATUS" stated "OVERDUE".

SECTION	ELEMENT	TASK	DUE DATE	STATUS	EVIDENCE
1	1A	Task 1	31-Dec-22	ONGOING	TBA
2	2A	TASK 2	1-Sep-23	ONGOING	TBA
3	3A	Task 3	1-Dec-22	ONGOING	TBA
4	4A	Task 4	1-Dec-22	ONGOING	TBA
5	5A	Task 5	1-Jan-22	OVERDUE	No
5	5B	Task 6	2-Jan-22	OVERDUE	No
6	6A	Task 7	2-Feb-22	CLOSED	Yes

So on my PBI table visual it would be like this:

SECTION	ELEMENT	TASK	DUE DATE	STATUS	EVIDENCE
5	5A	Task 5	1-Jan-22	OVERDUE	No
5	5B	Task 6	2-Jan-23	OVERDUE	No

[v-yanjiang-msft · Accepted solution]

Hi @Fahmie ,

1.If your expected result is the table in Power BI data view, you should do it in Power Query.

In the drop-down box of STATUS column, enter "OVERDUE" in the search box, then click "Select All Search Results"

After load into Desktop, the table also only contains the expected rows.

2.If you just want to get a visual only containing the expected rows, create a measure.

Measure =
IF ( CONTAINSSTRING ( MAX ( 'Table'[STATUS] ), "OVERDUE" ), 1, 0 )

Put the measure in the visual filter and set to 1.

After apply filter, get the expected result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fahmie]

Thank you so much! It's works!

So if i want to show just "ONGOING" Status, my measure shld be like this?

Measure =
IF ( CONTAINSSTRING ( MAX ( 'Table'[STATUS] ), "ONGOING" ), 2, 0 )

btw what is the meaning of 0 at the end of the measure?

[v-yanjiang-msft]

Hi @Fahmie ,
Yes, it is. But if you use 2 in the formula, in the visual filter, the value should be 2.

In the IF function, 0 is the result that does not meet the if condition. The logic is we first set the correct result is 1 and the incorrect result is 0. Then if we select 1 in the visual filter, the visual will only show values are correct.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Fahmie , Add a slicer on status column that will filter the visual

or an alternative

Text Filter Slicer and how to search on Multiple columns: https://youtu.be/RbeZRJ3uAZE