Forum Discussion
Power Bi formula - exclude rows when value equals filter
Hi all,
I have a very simple table example and am wondering if anyone can help me with this semi odd formula.
Basically I want to have a filter for "Fruit", when "Fruit" is filtered in a slicer to Pear for example nothing happens. But "fruit" is filterd for Pear & Apple, the table will filter out any rows where "fruit V2" equals a fruit selected in the slicer. E.g. in the below example rows 1,2,5 would be excluded.
Thanks
| Fruit | Fruit v2 |
| Pear | Apple |
| Apple | Apple |
| Banana | Orange |
| Pear | Orange |
| Apple | Pear |
Hi Jaypearce ,
I’ve completed your requirements using sample data and achieved the expected results. I’ve included snapshots of the output for your review.
When you have a chance, please let me know if this meets your expectations or if you have any suggestions for improvements.
FYI:
For Disconnected Table:
Fruit Slicer = DISTINCT(UNION( SELECTCOLUMNS(FruitData, "Fruit", FruitData[Fruit]), SELECTCOLUMNS(FruitData, "Fruit", FruitData[Fruit V2]) ))Used Measure:
Show Row = VAR SelectedFruits = VALUES('Fruit Slicer'[Fruit]) VAR SelectedCount = COUNTROWS(SelectedFruits) VAR FruitInFilter = MAX(FruitData[Fruit]) IN SelectedFruits VAR FruitV2InFilter = MAX(FruitData[Fruit V2]) IN SelectedFruits RETURN SWITCH( TRUE(), SelectedCount = 0, 1, SelectedCount = 1, 0, FruitInFilter && FruitV2InFilter, 1, 0 )Best Regards,
Community Support Team
12 Replies
Jaypearce Add a slicer visual to your report and set it to filter based on the "Fruit" column.
Create a DAX measure that checks if "Fruit v2" is in the selected values of the slicer.
SelectedFruits =
IF (
COUNTROWS ( VALUES ( Table[Fruit] ) ) > 1,
IF (
Table[Fruit v2] IN VALUES ( Table[Fruit] ),
0,
1
),
1
)Use the measure SelectedFruits as a filter in your table visual. Set the filter to show rows where SelectedFruits equals 1.
Hi Jaypearce ,
Teşekkürler Burak, jaypearce’in sorusu oldukça net ve bhanu_gautam’ın verdiği yanıt da doğru yolda ama biraz eksik kalmış. Kullanıcının istediği şey:
- Eğer slicer’da tek bir meyve seçilirse, tablo değişmesin.
- Ama slicer’da birden fazla meyve seçilirse, Fruit v2 değeri slicer’daki meyvelerden biriyle eşleşen satırlar gizlensin.
Bhanu’nun measure’ı doğru mantığı içeriyor ama görselde filtreleme yapacak şekilde uygulanmamış. Bu durumda, measure’ı bir görsel filtresi olarak kullanmak gerekiyor.
Aşağıda senin adına paylaşabileceğin, net ve uygulanabilir bir yanıt hazırladım:
Hey @jaypearce,
You're on the right track, and @bhanu_gautam’s logic is solid — just needs a small tweak to make it work as expected in the visual.
Here’s how you can do it:
Step 1: Add this measure
ShowRow = IF ( COUNTROWS ( VALUES ( Table[Fruit] ) ) > 1, IF ( Table[Fruit v2] IN VALUES ( Table[Fruit] ), 0, 1 ), 1 )Step 2: Use it as a visual-level filter
- Go to your table visual.
- Drag the ShowRow measure into the Filters on this visual pane.
- Set the filter to show only when ShowRow = 1.
This way:
- If only one fruit is selected → all rows are shown.
- If multiple fruits are selected → rows where Fruit v2 matches any selected fruit are excluded.
Let me know if you want to extend this logic to more complex scenarios — happy to help.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was supported by AI for translation and text editing.
Hi,
Thank you so much for your help so far, however this is where I have got stuck before... I get this error. Any ideas?
"A single value for column 'Fruit V2' in table 'Table' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Thank you
Hi Jaypearce ,
Thanks for reaching out to the Microsoft fabric community forum.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric CommunityRegards,
Menaka.Hi Jaypearce ,
The challenge comes from the difference between row context (calculated columns) and filter context (measures). If you try to do this with a measure referencing a column directly, you’ll hit the “single value cannot be determined” error.
Here’s the approach that always works for me:
1. Add a calculated column to your table:
ShowRow = VAR SelectedFruits = VALUES('Table'[Fruit]) RETURN IF( COUNTROWS(SelectedFruits) > 1 && 'Table'[Fruit v2] IN SelectedFruits, 0, // Exclude this row 1 // Include this row )2. In your visual, add a filter to only show rows where ShowRow = 1.
Hi Rohit1991,
Thank you and everyone else for the replies. Per v-menakakota advice I have uploaded a power bi file and data set to here.I have included a collumn called "include/exclude" for what I want the example to be. Do you know why your above formula might not be working with my data?
https://drive.google.com/drive/folders/1ze0vl4z6aCvf_Z2snzUamM9A7a30qp9N
Thanks
Hi all,
Thank you for your help so far rohit1991 v-menakakota burakkaragoz bhanu_gautam, however I still cannot get it to work...
Per v-menakakota advice I have uploaded the file example with the data set. I have added a column on the end for the results I want it to be "Include vs Exclude".
Can you please let me know if you can figure out what I am doing wrong?
https://drive.google.com/drive/folders/1ze0vl4z6aCvf_Z2snzUamM9A7a30qp9N?usp=drive_link
Thank you
JaydenHi all,
Thank you for your help so far rohit1991 v-menakakota burakkaragoz bhanu_gautam, however I still cannot get it to work...
Per v-menakakota advice I have uploaded the file example with the data set. I have added a column on the end for the results I want it to be "Include vs Exclude".
Can you please let me know if you can figure out what I am doing wrong?
https://drive.google.com/drive/folders/1ze0vl4z6aCvf_Z2snzUamM9A7a30qp9N?usp=drive_link
Thank you
JaydenHi Jaypearce ,
I’ve completed your requirements using sample data and achieved the expected results. I’ve included snapshots of the output for your review.
When you have a chance, please let me know if this meets your expectations or if you have any suggestions for improvements.
FYI:
For Disconnected Table:
Fruit Slicer = DISTINCT(UNION( SELECTCOLUMNS(FruitData, "Fruit", FruitData[Fruit]), SELECTCOLUMNS(FruitData, "Fruit", FruitData[Fruit V2]) ))Used Measure:
Show Row = VAR SelectedFruits = VALUES('Fruit Slicer'[Fruit]) VAR SelectedCount = COUNTROWS(SelectedFruits) VAR FruitInFilter = MAX(FruitData[Fruit]) IN SelectedFruits VAR FruitV2InFilter = MAX(FruitData[Fruit V2]) IN SelectedFruits RETURN SWITCH( TRUE(), SelectedCount = 0, 1, SelectedCount = 1, 0, FruitInFilter && FruitV2InFilter, 1, 0 )Best Regards,
Community Support TeamHi Jaypearce ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Thank you
