Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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.
MEASURE
Add the measure to the table filter as below
Hi @DTPBI ,
First we can start by creating such a table in PowerQuery:
And then create relationship with slicer table:
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:
Output:
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
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.
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.
MEASURE
Add the measure to the table filter as below
@DTPBI Hey,
you can use a custom visual Text Filter to get exact match value as result.
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
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks 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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |