Filtering Data by containing string values

[iping]

Figure 1.0 - design screen above is shown of my report.

I collected data shown in the table for preference of colors - the results are shown eg. Red, Yellow for the participants in survey.

What I need to acheive:

The horizontal three colors at the top, as shown in the image above, when clicked shouold filter for all rows that contains the selected color.

• The three (3) Colors at the top (horitonal layout) are an independant table that is created in the PowerBI,
• The dataset with the color choices and participant ID are  stored in another table named the ColorDS. See figure 2.0

\Figure 2.0 the tables in the PowerBI

[iping]

This is great.

Muchas Gracia.

[Icey · Accepted solution]

Hi @iping ,

Try this:

Measure 2 =
IF (
    SUMX (
        ColorTable,
        FIND (
            UPPER ( ColorTable[ChoiceList] ),
            UPPER ( MAX ( ColorsDS[Color Choice] ) ),
            ,
            0
        )
    ) > 0,
    1
)

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@iping , On of the way is to split the values using power query into the rows.

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Another way is use and independent slicer table to display color in the slicer and then use search or containsstring

measure =
var _max = selectedvalues(Color[Color])
return
calculate(countrows(colortable),filter(colortable,search(_max,colortable[Color],,0)>0))

refer: https://www.youtube.com/watch?v=mZt0HJw4gjQ

https://www.youtube.com/watch?v=XbgLGDvWdWQ

[iping]

@amitchandak  Thank you for the idea and post. ✌️

The second method using the DAX is preferred. However, what I observe from your online videos for the search function, seem to be that it will only find the text string entered. My results are shown below.

The function work but it will return results only if it says  "Red" , while I am seeking to return a result where the work Red can be in  a line as follows eg - Green, Red, Yellow or Yellow, Red.

Will look into the ContainString function.

---

@amitchandak wrote:

@iping, On of the way is to split the values using power query into the rows.

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Another way is use and independent slicer table to display color in the slicer and then use search or containsstring

measure =
var _max = selectedvalues(Color[Color])
return
calculate(countrows(colortable),filter(colortable,search(_max,colortable[Color],,0)>0))

refer: https://www.youtube.com/watch?v=mZt0HJw4gjQ

https://www.youtube.com/watch?v=XbgLGDvWdWQ

---