March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to 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
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
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
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:
Thanks in Advance
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
6 | |
3 | |
1 | |
1 | |
1 |