Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 exam...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
v-frfei-msft
Community Support
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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