Fixed & Dynamic Values with Slicer

[Anonymous]

Hi,

So I am currently struggling with a problem where I am trying use a slicer to filter a column of values, whilst also always keeping a value (not included in the slicer).

A simplified example of the data is:

Category	Year	Value
A	2018	11
A	2019	13
A	2020	19
B	2018	10
B	2019	12
B	2020	17
Fixed	2018	15
Fixed	2019	14
Fixed	2020	20

I want to be able to use a slicer to be left with:

Category	Year	Value	New Col
A	2018	11	11
A	2019	13	13
A	2020	19	19
Fixed	2018	15	15
Fixed	2019	14	14
Fixed	2020	20	20

When I filter to A, and 

Category	Year	Value	New Col
B	2018	10	10
B	2019	12	12
B	2020	17	17
Fixed	2018	15	15
Fixed	2019	14	14
Fixed	2020	20	20

when I filter to B. Any help or ideas would be much appreciated. To give a bit more context this is to create a waterfall to compare two categories (one being always fixed the other being dynamically selected).

[Anonymous · Accepted solution]

I managed to solve my problem but I feel like it is a non-optimal solution.

I did this by creating a calculated table and 3 measures:

slic = DISTINCT('Table'[Category]) // This is linked to the main table

Fixed Measure = CALCULATE(SUM('Table'[Value]), FILTER('Table', 'Table'[Category] == "Fixed"))

Swap Measure = SUM('Table'[Value])

Resulting Measure = CALCULATE([Fixed Measure], ALL(slic[Category])) + [Swap Measure]

The resulting measure column is what I was looking for, when you are slicing by the new "slic" table. If anyone has any recomendations that can implement the same functionality in an improved way please let me know.

[v-frfei-msft]

Hi @Anonymous ,

We can create a measure as below and put it to the visual.

Measure =
VAR a =
    SELECTEDVALUE ( slic[Category] )
RETURN
    IF (
        ISFILTERED ( slic[Category] )
            && a = MAX ( 'Table'[Category] ),
        BLANK (),
        1
    )

[Anonymous]

Hi @v-frfei-msft , sorry but this solution filters to the two categories not selected, is there a way of doing so that we can always have Fixed shown and switch between the other two? Thanks