Forum Discussion
Power Bi formula - exclude rows when value equals filter
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
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
