Simple slicer issue: Only show two options: filter, or show all

[Anonymous]

This has to be an easy one, but I'm struggling.  I have a field with Y and N as the only values. The user wants to have only two options: show All or filter out the Y's. They don't want three options of Y, N, or Select All, as a slicer usually behaves.

So basically there's a chexbox (slicer) called "include ICCS?" If the user selects it, we want all rows to be displayed. If the box isn't checked, then they want the rows where the field ICCS = Y filtered out.

This dataset has millions of rows so it needs to be efficient. It should be so simple but I just can't get my head around it.

thanks!

[Ninja23]

I have exact same problem. I have one table with 'match' column and want to have slicer with options ALL or ONLY MATCHED. Could someone help me? The solution is not working or I am not applying it correctly.

[Icey]

Hi @Anonymous ,

Please check if this is what you want:

Create a Measure like so:

Measure = 
IF (
    SELECTEDVALUE ( 'Filter Pre-ICCS'[preICCS] ) = "Y",
    1,
    IF (
        SELECTEDVALUE ( 'Filter Pre-ICCS'[preICCS] ) = "N"
            && MAX ( Sheet1[ICCS] ) = "N",
        1
    )
)

Then put it on Visual filter and select Measure = 1.

Best Regards,

Icey

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

[Anonymous]

@Icey  Apparently I spoke too soon, this isn't quite working. This simple example is fine, but I've been trying to understand how the

MAX ( Sheet1[ICCS] ) = "N"

part works, I can't quite get my head around it. Max is doing some type of aggregation I would think, but it seems like this data needs to be filtered at the row level, since any row can be Y or N. 

Once I implemented this using my real data, I noticed that I have two charts (a table and a bar chart) which should be giving different views of identical data, but they're not. One of the charts includes a "status" field, and when that's added to the visual, the grand totals and aggregations change, which shouldn't happen. I thinking this may be an issue where CALCULATE or ALL is needed, something to determine when the logic is applied, but I still struggle with that aspect of DAX.

I can post an actual .pbix if I mask the data, but before going to that effort, I was wondering if the description of my issue might be enough for you to offer an obvious solution?

thanks!

Alex 

[Icey]

Hi @Anonymous ,

In Measures, MAX or MIN can be used to get the value of current row.

If you can share me a dummy PBIX file, with some sample data whose structure is the same as your real data, it will be great. For data security, please don't share your real data.

Best Regards,

Icey

[amitchandak]

You have to use isfilteredand hasonefilter. and force formula using all  and =

refer

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

if(isfiltered(Table[Value]), Measure1,Balnk())//Check hasonevalue

Measure1 is the value you can have with filter

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Anonymous]

@amitchandak  Thank you for the quick reply, but I still can't figure it out and was wondering if you might still be able to help? I just can't figure out quite where that measure goes, or how I use it to filter. I'm not sure how or when to use hasonevalue, or when it would ever be true, as my dataset will likely alwatys include Y and N in it.

Here's a simple dataset:

If I select N then I want to see only the N records:

 

If I select Y then I want to show all:

This simple .pbix is found here

Thanks!