cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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:

CategoryYearValue
A201811
A201913
A202019
B201810
B201912
B202017
Fixed201815
Fixed201914
Fixed202020

 

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

CategoryYearValueNew Col
A20181111
A20191313
A20201919
Fixed20181515
Fixed20191414
Fixed20202020

 

When I filter to A, and 

CategoryYearValueNew Col
B20181010
B20191212
B20201717
Fixed20181515
Fixed20191414
Fixed20202020

 

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

View solution in original post

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

Capture.PNG

 

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

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors