Forum Discussion
Solved
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 slice...
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 ,
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.