Find and filter a text in column

[GrandeMariela]

Hi team, could you please give me some ideas?

I have two data sources to group by, something like this: 

= Table.Group(#"Added Custom1", {"name", "material", "country", "prod_line"},

{{"TOTAL", each List.Sum([TOTAL]), type nullable number},

{"product_color", each Text.Combine([product_color], ";"), type nullable text},

{"Ids", each Text.Combine([Index], ";"), type nullable text}})

then I have a result like this 

I need when select white or black from a slicer show the table for bedroom, same case selecting grey or silver show table for office. I can't just write product_color after prod_line because grouping will not do it correctly according with requirements. I tried with shearch() and find() function but doesn't work. show me an error 

filter for prod_color = FIND("silver",products_store[product_color])

I tried this too, don't work. 

filtered prod_color = SELECTEDVALUE(products_store[color], "no")

filter for prod_color = FIND(products_store[filtered prod_color],products_store[product_color])

Do you have some ideas to solve this. Thanks in advance

[shafiz_p · Accepted solution]

Hi @GrandeMariela  To acheive such filter using slicer, you need to create a calculated table with single column values are distinct color.

datatable:

Try the below code to create a new distinct color table:

ColorTable = 
DISTINCT (
    SELECTCOLUMNS (
        GENERATESERIES (1, PATHLENGTH(SUBSTITUTE(CONCATENATEX('datatable', 'datatable'[product_color], ";"), ";", "|"))),
        "Color", 
        TRIM (
            PATHITEM (
                SUBSTITUTE ( CONCATENATEX ( 'datatable', 'datatable'[product_color], ";" ), ";", "|" ),
                [Value]
            )
        )
    )
)

 See Image Also:

Write a color search measure:

Color_Search = 
VAR searchvalue =
    SELECTEDVALUE (ColorTable[Color] )
RETURN
    IF (
        CONTAINSSTRING (
            CONCATENATEX ( 'datatable', 'datatable'[product_color], ";" ),
            searchvalue
        ),
        1,
        0
    )

Create slicer and table visual and place the newly created measure in "Filter on visuals" option:

That's it!! Now if you select color in slicer, and the table will filter accordingly. See the output:

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[shafiz_p · Accepted solution]

Hi @GrandeMariela  To acheive such filter using slicer, you need to create a calculated table with single column values are distinct color.

datatable:

Try the below code to create a new distinct color table:

ColorTable = 
DISTINCT (
    SELECTCOLUMNS (
        GENERATESERIES (1, PATHLENGTH(SUBSTITUTE(CONCATENATEX('datatable', 'datatable'[product_color], ";"), ";", "|"))),
        "Color", 
        TRIM (
            PATHITEM (
                SUBSTITUTE ( CONCATENATEX ( 'datatable', 'datatable'[product_color], ";" ), ";", "|" ),
                [Value]
            )
        )
    )
)

 See Image Also:

Write a color search measure:

Color_Search = 
VAR searchvalue =
    SELECTEDVALUE (ColorTable[Color] )
RETURN
    IF (
        CONTAINSSTRING (
            CONCATENATEX ( 'datatable', 'datatable'[product_color], ";" ),
            searchvalue
        ),
        1,
        0
    )

Create slicer and table visual and place the newly created measure in "Filter on visuals" option:

That's it!! Now if you select color in slicer, and the table will filter accordingly. See the output:

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[GrandeMariela]

Hi Shahariar Hafiz, could you please help me with this issue I have now. After do it like you told me a cards I had in report are not filtering by slicer with ColorTable. I tried added same search measure (doesn't work in cards) and make a relatioship but only takes the sigles ones. I mean color combined like grey;silver are out of relatioship. 
Do you have any idea can help me. I really appreciate your help, thanks in advance. 

[shafiz_p · Accepted solution]

The solution provided according to your problem. Now I don't know what you are trying to visualize in cards. For example, If I decided to show totals in cards and want slice by color, then the measure could be like:

Total_Value = 
VAR selectedColor = SELECTEDVALUE(ColorTable[Color])
RETURN
CALCULATE(
    SUM('datatable'[total]),
    FILTER(
        'datatable',
        CONTAINSSTRING('datatable'[product_color], selectedColor)
    )
)

This measure will calculate totals based on slicer selection. So, basically 2 measure, one for table and this one for cards to visualize totals. See image below:

Hope this helps!!
If this solved your problem, please accept it as a solution!!

[GrandeMariela]

Thank you, I am new developing Power BI, your detailed answer helps me a lot

[Jpss]

hey @GrandeMariela 
i think we need a different approach here. 
you need to transform the table to look like this : 

once done, you can have a colors table, and createa relationship between them

here is the solution

Please see the pbix file for your reference
filterng products _jpss.pbix