Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SujoyDasgupta
Frequent Visitor

Filter on comma separated multiselect field

Hi Guys,

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

Region.jpg

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.

 
 
 

Region.jpg

 

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. 

Region.jpg

 

Need help on this. Any idea or solution appreciated.

 

Thanks in Advance.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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:

97.gif

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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:

97.gif

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft ,

 

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

 

Thank you for your reply. 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lbendlin
Super User
Super User

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) .

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. Relationship.jpg

 

 

 

Thanks in Advance

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.