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 

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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.