Filter out only some rows

[M3IT]

My data has a column with one of 3 values - say A, B & C.

I want a table that always displays rows with A or B and a slicer that selects whether C rows are displayed.

So:

     Slicer ticked: Display all data (A, B & C)

     Slicer unticked: Display only rows with A or B

Is this possible?

I'm currently using a simple slicer, but this allows the user to turn off A & B. Is there a way to lock certain values on?

[AndrejZitnay]

Hello @M3IT 

I would recomment to look into slicer control and change setting. 

You can add filters as well to whole report, page or individual visual. 

I would recomend for you to look into guided learing :

https://docs.microsoft.com/en-us/power-bi/guided-learning/

This is very helpfull.

Kind regards.

Andrej

[M3IT]

Thanks for the response @AndrejZitnay, but nothing in the suggested learning that I can find answers the question. A basic slicer doesn't do what I need because I want to display some rows regardless of the slicer selection.

[v-xuding-msft · Accepted solution]

Hi @M3IT ,

You could try to create a new irrelated table as a slicer. I created a simple sample that you can reference.

• Create a new table munally

• Create a measure

Measure = 
var A_B = CALCULATE(SUM('Table 1'[Values]),FILTER('Table 1','Table 1'[Items] = "A" || 'Table 1'[Items] = "B"))
var C = CALCULATE(SUM('Table 1'[Values]),FILTER('Table 1','Table 1'[Items] = "C"))
return
IF(ISFILTERED('Table'[Filter Table]),C,A_B)

 

Best Regards,

Xue Ding

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

[M3IT]

That's wonderful @v-xuding-msft.

It doesn't do exactly as I was asking for, but with a small amendment it works great.

I changed the Measure to:

Measure =

var A_B = CALCULATE(SUM('Table 1'[Values]),FILTER('Table 1','Table 1'[Items] = "A" || 'Table 1'[Items] = "B" ))

var ABC = CALCULATE(SUM('Table 1'[Values]))

return

IF(ISFILTERED('Table'[Filter Table]),ABC,A_B)

Thanks

MB