Advanced Filter to always show one value irrespective of the filter selection

[Anonymous]

Hi, 

I have 2 field which is as below;

Type	Value
HP	1
HP	2
HP	3
HP	4
HP	5
HP	6
HP	7
HP	8
HP	9
HP	10
AC	0
AC	0
AC	0
AC	0
AC	0
AC	0
AC	0

I have a slicer for this and I want to always show AC (and its value which is zero always) iresspective of the slicer selection

Example: If I select slicer from 5 to 10, I want to show all HP values from 5 to 10 plus all AC rows as well

Is there a  DAX code for this or any advance Filter options? Thanks !

[Anonymous · Accepted solution]

Hi @Aby17,

You can take a look at the following steps if they suitable for your requirement.

1, Add an Index Column to show all AC rows at query editor side.

2, Create a calculated table: 

Slicer store = DISTINCT('Type-Value'[Value])

3, Add new measure:

Measure 2 =
IF (
    SELECTEDVALUE ( 'Type-Value'[Type] ) = "AC",
    SUM ( 'Type-Value'[Index] ),
    IF (
        SUM ( 'Type-Value'[Value] ) IN FILTERS ( 'Slicer store'[Value] ),
        SUM ( 'Type-Value'[Index] ),
        BLANK ()
    )
)

If above doesn't work, share some sample data and expected output will be help.

Best Regards,

Jack

[vanya_val]

Hi,

I have a similar problem with one field only. When applying a filter to that field I want the result to always show one of the options, as well as the selected option.

The field options are as below:

Deal Stage 1

Deal Stage 2

Deal Stage 3

Deal Stage 4

Deal Stage 5

(Blank)

When a user selects a certain deal stage I want all values with a (Blank) to also remain in the result. It also needs to be possible to multi-select stages and still include (Blank) in the result.

I'm very new to DAX and any help with this is greatly appreciated!