Forum Discussion
Helper Isingle slicer on two columns
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]
)
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
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]
)