Filter hundred of values

[manhuynguyen]

Dear Power BI users,

I have a filtering issues that needs your support:

The data I have looks like this:

Item	Category
A	X1; X3; X5
B	X3; X5; X7
C	X4; X8; X9
D	X5; X8
E	X11; X12
F	X9; X5

Now I would like to see those Items that have "X3 or X5 or X7". In this case, item C & E will NOT appear.

What would be the way to do this effectively? Given that my actual data has hundred of categories need to be filtered.

Thanks a lot,

[Dinesh_Suranga]

@manhuynguyen 

Did you change table name and column name according to your data table?

Thanks

[manhuynguyen]

Hi @Dinesh_Suranga, yes I did. Would you help me to see what could be wrong with my queries as above? Thanks

[Dinesh_Suranga · Accepted solution]

@manhuynguyen 

Hi 

I gave you a DAX code. You can not use it in power query side.
Please try following M code in power query.

if Text.Contains([Category] , "X3") or Text.Contains([Category] , "X5") or Text.Contains([Category] , "X7") then "True" else "False"

 Thank you.

[manhuynguyen]

Sorry my bad, but when I tried it in DAX, it's still not return desirable outcome

So thank you very much, I'll accept this as a solution.

But when I tried in Power Query, it works perfectly.

[Dinesh_Suranga]

@manhuynguyen 

Hi,

Please try following DAX code.

Measure = CONTAINSSTRING( SELECTEDVALUE('Table'[Category]),"X3") || CONTAINSSTRING( SELECTEDVALUE('Table'[Category]),"X5") || CONTAINSSTRING( SELECTEDVALUE('Table'[Category]),"X7") 

Then you can filter True values.

If this helps you to solve your issue. Please accept this as the solution.

Thank you.

[manhuynguyen]

Hi, 

I tried it but it showed error: "Token eof expected"

Could you send me your pbix file as an example for this?

I'd really appreciate it.

Best regards,