Using a Yes/No Measure As a Slicer/Filter

[sv98917n]

Hello Community, 

I have a measure that returns Yes/No based on duplicate values that is dependant on a date slicer (which is why its in a measure). Is there a way to make a yes/no drop down page filter based on these values. My research tells me that this is impossible, but is there any work around?

My measure is:

Duplictate Segment ID (Y/N) = IF (COUNTROWS ( FILTER ( allselected(table1), table1[SEGMENT_ID] = max ( table1[SEGMENT_ID] ) ) ) >= 2, "YES", "NO" )

Any help would be greatly appreciated. Thank you. 

[Greg_Deckler]

@sv98917n - In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[sv98917n]

Thank you for your reply. This works slightly. When I apply this method and I select "Yes", the other values that would be "No" are there and not filtered out. They just appear as blank. Any insight? Thank you. 

[v-yingjl · Accepted solution]

Hi @sv98917n ,

1. Create a Slicer table manually and use it as a slicer:

2. Create a measure like this:

Measure = 
VAR _count =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[date] ) )
RETURN
    IF (
        NOT ( ISFILTERED ( Slicer[Slicer] ) ),
        _count,
        IF (
            SELECTEDVALUE ( Slicer[Slicer] ) = "Yes"
                && _count >= 2,
            "Yes",
            IF ( SELECTEDVALUE ( Slicer[Slicer] ) = "No" && _count < 2, "No" )
        )
    )

default statususing a slicer

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@sv98917n , You have to create Table with yes and no.

And use along with this measure at a correct row context

refer

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...