How to show BI slicer with unique value and do table visualization filter?

[Anonymous]

I have single SharePoint list, at backend Audience colunm have multiselect lookup.
When I get list in Bi, the Audince column value show in ; seperated formate in table
Ex: audience1;  audience2; and so on
When I assign this column to slicer, Slicer look like:
audience1;  audience2;

audience1

audience1;  audience3;

Etc.
So how i pass unique value to slicer? and my table visual performed filter based on column

[Anonymous · Accepted solution]

Hi @Anonymous 

You may try my way to build the slicer column by dax.

Build a new table by dax.

Slicer Audiance = 
VAR myvalues =
    ADDCOLUMNS (
        'Table',
        "AudincePaths", SUBSTITUTE ( 'Table'[Audince], ";  ", "|" )
    )
RETURN
    VAR myvalues2 =
        ADDCOLUMNS (
            myvalues,
            "AudincePaths2", SUBSTITUTE ( [AudincePaths], ";", "|" )
        )
    RETURN
        VAR _T =
            SELECTCOLUMNS (
                GENERATE (
                    myvalues2,
                    ADDCOLUMNS (
                        GENERATESERIES ( 1, PATHLENGTH ( [AudincePaths2] ) ),
                        "MyAudince", PATHITEM ( [AudincePaths2], [Value], TEXT )
                    )
                ),
                "Audince", [MyAudince]
            )
        RETURN
            SUMMARIZE ( FILTER ( _T, [Audince] <> BLANK () ), [Audince] )

Slicer Table:

Then we build a measure to filter the table by slicer.

Measure:

Measure = IF(CONTAINSSTRING(MAX('Table'[Audince]),SELECTEDVALUE('Slicer Audiance'[Audince])),1,0)

Build a table visual by Table[Audiance], then drag measure into this visual and set it show items if value =1.

Result is as below.

When I select "audience1"

When I select "audience2"

You can download the pbix file from this link:  File

Best Regards,

Rico Zhou

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

[amitchandak]

@Anonymous , does column contains values like

audience1; audience2;

audience1

audience1; audience3;

If so you might have split data on ;

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

[Anonymous]

Thank you @amitchandak. Can you suggest other method than split?