Forum Discussion

GrandeMariela's avatar
GrandeMariela
Frequent Visitor
1 year ago
Solved

Find and filter a text in column

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

Need Help
Tips and Tricks
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    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's avatar
    shafiz_p
    1 year ago

    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!!

     

     

     

5 Replies

  • shafiz_p's avatar
    shafiz_p
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      GrandeMariela
      Frequent Visitor
      1 year ago

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

    • GrandeMariela's avatar
      GrandeMariela
      Frequent Visitor
      1 year ago

      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's avatar
        shafiz_p
        Icon for Super User rankSuper User
        1 year ago

        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!!

         

         

         

  • Jpss's avatar
    Jpss
    Icon for Resolver II rankResolver II
    1 year ago

    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