Forum Discussion

cturner's avatar
cturner
Icon for Helper I rankHelper I
8 years ago
Solved

single slicer on two columns

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 ...
Need Help
Tips and Tricks
Tutorial Requests
  • cturner's avatar
    cturner
    8 years ago

    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]
    )

cturner's avatar
cturner
Icon for Helper I rankHelper I
8 years ago

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's avatar
cturner
Icon for Helper I rankHelper I
8 years ago

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]
)