Forum Discussion

SujoyDasgupta's avatar
SujoyDasgupta
Frequent Visitor
5 years ago
Solved

Filter on comma separated multiselect field

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 co...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

lbendlin's avatar
lbendlin
Super User
5 years ago

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's avatar
    SujoyDasgupta
    Frequent Visitor
    5 years ago

    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