Forum Discussion

Jaypearce's avatar
Jaypearce
Frequent Visitor
1 year ago
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...
Need Help
  • v-menakakota's avatar
    v-menakakota
    1 year ago

    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 

rohit1991's avatar
rohit1991
Super User
1 year ago

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.