Forum Discussion

iping's avatar
iping
New Member
5 years ago
Solved

Filtering Data by containing string values

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.  ...
Interesting Links
Need Help
Tutorial Requests
  • Icey's avatar
    Icey
    5 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
    iping
    New Member
    5 years ago

    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