Forum Discussion

sbott's avatar
sbott
Helper I
2 years ago
Solved

Filter based on content

Hi, I have a list of data in a column, some of the data contains the letters CE and some contains F&O. 

 

I need a filter with two options, CE or F&O, that when selected, only selects the data which contains either CE or F&O.

 

Just remembered to add the fact that I don't have access to change the source data, so have to use the values supplied for the report.

 

How can I achieve this?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solution hackcrr  offered, and i want to offer some more information for user to refer to.

    hello sbott , based in your description, you cannot change the datasource, you can craete a measure to filter the data, you can refer to the following solution.

    Sample data 

    Create a measure.

    Count = COUNTROWS(FILTER('Table',CONTAINSSTRING([Word],"CE")||CONTAINSSTRING([Word],"F&O")))

    Then put the measure to the visual filter.

     

     

    Output

    Best Regards!

    Yolo Zhu

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

     

     

     

3 Replies

  • hackcrr's avatar
    hackcrr
    Memorable Member
    2 years ago

    Hi, sbott 

    If you need a filter that allows users to select only data containing "CE" or "F&O" from a column without changing the source data, you can accomplish this by creating a calculated column or metric in Power BI. You can do the following.
    Create a new calculated column:

    FilterFlag = 
IF(
    CONTAINSSTRING([YourColumnName], "CE") || CONTAINSSTRING([YourColumnName], "F&O"),
    "Selected",
    "Not Selected"
)

    You can now create slicers based on newly created calculated columns, and users can now select "Selected" or "Unselected" to filter data containing "CE" or "F&O".

     

    Best Regards,

    hackcrr

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

  • sbott's avatar
    sbott
    Helper I
    2 years ago

    Hi hackcrr I am not able to add any columns to the data, I just have access to the all the tables and contents, but not to alter it.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

    Thanks for the solution hackcrr  offered, and i want to offer some more information for user to refer to.

    hello sbott , based in your description, you cannot change the datasource, you can craete a measure to filter the data, you can refer to the following solution.

    Sample data 

    Create a measure.

    Count = COUNTROWS(FILTER('Table',CONTAINSSTRING([Word],"CE")||CONTAINSSTRING([Word],"F&O")))

    Then put the measure to the visual filter.

     

     

    Output

    Best Regards!

    Yolo Zhu

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