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
DTPBI
Frequent Visitor

How to filter table where a column has multiple comma delimited values

Hi,

Hoping someone can help whether this scenario is possible....and if so how to achieve it.

I have a table that has comma seperated values in a single column (see Fruits table in Red below). I want to be able to filter the table for rows that exactly match/contain the values selected in the slicer (slicer in Blue below). E.g if someone selects Apple & Pear in the slicer then row 3 will be the result in the Fruits table.

DTPBI_0-1712730690390.png

 

1 ACCEPTED SOLUTION

Thanks but this didn't work as expected. When selecting all options it showed all rows when it should only show rows if all options were assigned in the Fruits column (comma seperated) to the row.

However, I have managed to work it out.


TABLES

DTPBI_1-1713324762614.png


MEASURE

Measure =
VAR selValues = CONCATENATEX(VALUES(Slicer[Fruit]),Slicer[Fruit],", ",Slicer[Fruit],ASC)
VAR selCount = COUNTROWS(ALLSELECTED(Slicer[Fruit]))
VAR sTableselCount = CALCULATE(COUNTROWS(Table),Table[AssignedCount]=selCount)
VAR sTableselValues = CALCULATE(COUNTROWS(Table),Table[Fruits]selValues)
VAR sResults = IF(sTableselCount>0 && sTableselValues >0,1)
RETURN
IF(NOT ISFILTERED(Slicer[Fruit]),0,sResults)



Add the measure to the table filter as below

DTPBI_0-1713324145357.png

 

View solution in original post

10 REPLIES 10
v-cgao-msft
Community Support
Community Support

Hi @DTPBI ,

First we can start by creating such a table in PowerQuery:

vcgaomsft_0-1712825437375.png

And then create relationship with slicer table:

vcgaomsft_1-1712825555363.png

Create a new measure like:

 

Measure = 
VAR __cur_fruit = SELECTEDVALUE('Table1'[FRUITS])
VAR __fruits = VALUES('Table2'[FRUITS])
VAR __slicer_count = COUNTROWS(ALLSELECTED('Slicer'[SLICER]))
VAR __table_count = CALCULATE(COUNTROWS('Table2'),'Table2'[FRUITS] IN __fruits && 'Table2'[FRUITS]=__cur_fruit)
VAR __result = IF(__cur_fruit IN __fruits && __slicer_count = __table_count,1)
RETURN
__result

 

And apply it to the visual's filter:

vcgaomsft_2-1712825630971.png

Output:

vcgaomsft_4-1712825705133.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Thanks for the quick response. I may not have been very clear in my original post. I need only results that match exactly what has been selected in the slicer. So for your exampe of Apple & Pear selected, only the row Apple, Pear should be returned and not other rows that contain ether Apple or Pear.

Hi @DTPBI ,

Please try this measure:

Measure = 
VAR __cur_fruit = SELECTEDVALUE('Table1'[FRUITS])
VAR __fruits = VALUES('Table2'[FRUITS])
VAR __slicer_count = COUNTROWS(ALLSELECTED('Slicer'[SLICER]))
VAR __table_count = CALCULATE(COUNTROWS('Table2'),'Table2'[FRUITS] IN __fruits && 'Table2'[FRUITS]=__cur_fruit)
VAR __table_count_2 = CALCULATE(COUNTROWS('Table2'),'Table2'[FRUITS]=__cur_fruit,ALLEXCEPT('Table2','Table2'[FRUITS]))
VAR __result = IF(__cur_fruit IN __fruits && __slicer_count = __table_count && __slicer_count = __table_count_2, 1)
RETURN
__result

vcgaomsft_0-1712898709338.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Thanks again for your assistance. I managed to get it working based on my original post and modifying some of your logic using this measure:

Note: Fruits[AssignedCount] is a calculated column in the Fruits table to count number of assigend fruits per row.

Measure =
VAR selValues = CONCATENATEX(VALUES(Slicer[Fruit]),Slicer[Fruit],", ",Slicer[Fruit],ASC)
VAR selCount = COUNTROWS(ALLSELECTED(Slicer[Fruit]))
VAR sFruitsselCount = CALCULATE(COUNTROWS(Fruits),Fruits[AssignedCount]=selCount)
VAR sFruitsselValues = CALCULATE(COUNTROWS(Fruits),Fruits[Fruits]= selValues)
VAR sResults = IF(sFruitsselCount>0 && sFruitsselValues >0,1)
RETURN
sResults

I then add the above measure to the table as a filter and select show items where Is Not Blank.

I still have one issue, while this works perfectly when there are slections in the slicer, it does not display any rows in the table where no selections have been made in the slicer. I would like the table to display all rows in that sceanrio.

Hi,

PBI file attached.

Ashish_Mathur_0-1713323240411.pngAshish_Mathur_1-1713323248718.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks but this didn't work as expected. When selecting all options it showed all rows when it should only show rows if all options were assigned in the Fruits column (comma seperated) to the row.

However, I have managed to work it out.


TABLES

DTPBI_1-1713324762614.png


MEASURE

Measure =
VAR selValues = CONCATENATEX(VALUES(Slicer[Fruit]),Slicer[Fruit],", ",Slicer[Fruit],ASC)
VAR selCount = COUNTROWS(ALLSELECTED(Slicer[Fruit]))
VAR sTableselCount = CALCULATE(COUNTROWS(Table),Table[AssignedCount]=selCount)
VAR sTableselValues = CALCULATE(COUNTROWS(Table),Table[Fruits]selValues)
VAR sResults = IF(sTableselCount>0 && sTableselValues >0,1)
RETURN
IF(NOT ISFILTERED(Slicer[Fruit]),0,sResults)



Add the measure to the table filter as below

DTPBI_0-1713324145357.png

 

@DTPBI Hey,
you can use a custom visual Text Filter to get exact match value as result.

HarishKM_0-1712901823845.png

 

 

Thanks,

Harish

 

Did I answer your question? Kindly give kudos and Mark my post as a solution!

Thanks, while this works it expects the users to type in the values comma seperated correctly. Fruits is an example but the actual values in our data are a lot longer

MFelix
Super User
Super User

Hi @DTPBI ,

 

Try the following measure:

Filter Measure = 
    IF( SUMX(Slicer,
        FIND(
            Slicer[Fruit],
            MAX(Fruits[Fruits]),,0)) > 0, 1)

Then use this one on the table visualization has a filter.

 

MFelix_0-1712761816604.pngMFelix_1-1712761827812.png

 

MFelix_2-1712761838531.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



DTPBI
Frequent Visitor

Thanks for the quick response. I may not have been very clear in my original post. I need only results that match exactly what has been selected in the slicer. So for your exampe of Apple & Pear selected, only the row Apple, Pear should be returned and not other rows that contain ether Apple or Pear.

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.