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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.