single slicer on two columns

[cturner]

Ok, here's my requirement:

I have two columns - they are actually in a dimension table but I can move them to the fact, so that's a non-issue

Call them:

Is RED

Is BLUE

there is some crossover between RED and BLUE.  Maybe it's purple.

they have true/false values, but again, I can change those values to whatever.  That part is easy.

What's evading me is HOW to create a single slicer that covers both columns.  I have a dummy table with RED and BLUE for the slicer, and if it was a measure it would be trivial to use the switch function to swap out measures.  But since this is a dimension, and I want to filter the entire fact table on it, it's a little tricker.

So how do I create a slicer with Red and Blue as options that lets me select all the rows in the master fact table that are flagged RED, all the ROWS that are BLUE, and all rows unfiltered when the slicer is unselected?

This seems like it should be simple, but every approach I've tried has been a dead end.  Thanks.

[Anonymous]

I dont think the answer is going to be as simple as you hope it to be.  Either you will need some calculated column that performs some logic considering Both columns, but that logic would need to be static.  Or you might need to consider unpivoting the data such that instead of having a True/False for each of the red/blue columns that you have a 'Colour' column and have power BI convert all of the True values into being a corresponding value in that one column.

Naturally which direction you go will be influenced by the overall picture.

Of course you could also use multiple slicers depending on what the colours mean and how the users will be actually using the reports.

[cturner]

I've tried created a calculated column that is a combination of both columns.  That works, but it leaves me with a third option "BOTH" that seems logically nonsensical.  Why should I have to say BOTH when that's not what is important to the consumer of the report.  It's only if it's red or blue that concerns them.

As far as exotic approaches, I tried this, which works ONLY at the level of granularity that red and blue is supplied.  For other data cuts it fails:

RB filter = if(HASONEFILTER('RB'[RB Switch]),
switch(FIRSTNONBLANK('RB'[RB Switch],'KAP/HPO'[RB Switch])
,"RED",iferror(VALUES('rbc Client'[isRED]),"FALSE")
,"BLUE",IFERROR(VALUES('rbc Client'[isBLUE]),"FALSE")
),"TRUE")

and then setting a visual level filter to RB filter = TRUE.   

Again, this works for the granularity that the red/blue filter is supplied, but rolled up to other levels it fails.

[cturner · Accepted solution]

After a LOT of beating my head against this and barking up the wrong tree (calculatedtable was NOT the answer) I was able to solve it by filtering the measures and not the dimensions.  I still forget this maxim from time to time.  This one was painful enough that I probably won't forget it anytime soon.

So, here's my logic for one measure.  I have to filter all the measures in the output in the same way for this approach to work.

Revenue =
IF (
    HASONEFILTER ( 'Red/Blue'[RB Switch] ),
    SWITCH (
        MAX ( 'Red/Blue'[RB Switch] ),
        "RED", CALCULATE (
            [revcalc],
            FILTER ( 'revenuetable', RELATED ( 'color'[isRed] ) = TRUE () )
        ),
        "BLUE", CALCULATE (
            [revcalc],
            FILTER ( 'revenuetable', RELATED ( 'color'[isBlue] ) = TRUE () )
        )
    ),
    [revcalc]
)