cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Fixed & Dynamic Values with Slicer

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I

## Re: Fixed & Dynamic Values with Slicer

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.
3 REPLIES 3
Highlighted
Community Support

## Re: Fixed & Dynamic Values with Slicer

Hi @Davidson919 ,

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Helper I

## Re: Fixed & Dynamic Values with Slicer

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

Highlighted
Helper I

## Re: Fixed & Dynamic Values with Slicer

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.

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.