Filter on comma separated multiselect field

[SujoyDasgupta]

Hi Guys,

In my Datatable there is a comma separated multi select field "Region" like below image.

I want to create a dropdown of Unique Regions based on this Region field. But I am getting comma separated values in my dropdown like below image.

Can you please help me to create this dropdown with unique values of Region and based on that dropdown I want to filter my Datatable like below image.

NOTE: I dont want to split my region column in multiple ROWS. It will not full fill my client requirement. 

Need help on this. Any idea or solution appreciated.

Thanks in Advance.

[v-shex-msft · Accepted solution]

Hi @SujoyDasgupta,

The basic filter effect not able to achieve your requirement, I think you need to some more steps and tries.
First, create a new table to extract and expand all types of regions that existed in your table and use it to create a slicer. (notice: it does not have a relationship to the raw table)

Expand = 
VAR _path =
    SUBSTITUTE (
        CONCATENATEX ( VALUES ( 'Table'[Region] ), [Region], "," ),
        ",",
        "|"
    )
RETURN
    DISTINCT (
        SELECTCOLUMNS (
            ADDCOLUMNS (
                GENERATESERIES ( 1, PATHLENGTH ( _path ), 1 ),
                "Desc", PATHITEM ( _path, [Value] )
            ),
            "Desc",IF([Desc]<>"", [Desc]," ")
        )
    )

Second, write a measure to compare raw table records and slicer selections to return tag and apply on it on table 'visual level filter' to filter records.

Measure = 
VAR curr =
    CONCATENATEX (
        VALUES ( 'Table'[Region] ),
        IF ( [Region] <> "", [Region], " " ),
        ","
    )
RETURN
    IF (
        COUNTROWS (
            FILTER ( ALLSELECTED ( Expand[Desc] ), SEARCH ( [Desc], curr,, -1 ) > 0 )
        ) > 0,
        "Y",
        "N"
    )

Result:

Regards,

Xiaoxin Sheng

[SujoyDasgupta]

Hi @v-shex-msft ,

I will try to implent your above solution and update you.

Thank you for your reply. 

[v-shex-msft]

HI @SujoyDasgupta,

Ok, I hope these can be worked on your side.🙂

If they not help, you can feel free to post here with some dummy data or pbix file, then we can test to coding formula and build sample file share.

Regards,

Xiaoxin Sheng

[lbendlin]

You need to do that before Power BI (for example in Power Query) or as a DAX table definition.

Please provide sample data in usable format (not as a picture) .

[SujoyDasgupta]

Hi @lbendlin ,

I have uploaded the data in my Google Drive for your easy access. Please follow the below URL to acess the data.

https://drive.google.com/file/d/1Gej5I-9hxqh8KokUErrPLPRn4jz2KSQv/view?usp=sharing 

I have achived the Filter on comma separated multi select field using below steps. Can you confirm is it the right approach or we can do it other way more effectively.

I have followed belo steps:

1. Duplicate the Data table in another new table.
2. Split this new table Row wise based on the comma separated region.
3. Create a dropdown using slicer based on new table.
4. In result section we will show old table with value of comma separated region.
5. Finally we have created one Relationship between OLD & NEW table.
6. We have set relation between ID of both the table.  Set Cardinality - Many to One, Cross filter direction - Both and Activate the relationship. 

Thanks in Advance