Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Measure as a drop down slicer

Hi,

 

There is a measure "TotalCost" which used in table visual along with few other columns.  I want to filter the table visual by using the same measure in dropdown slicer with multiselect option.

I have created a calculated table using below dax:

Slicer Measure =
  VAR Table1 =        
  SUMMARIZE(
    (Product),
    Product[ProductName],
    Product[ProductNumber],
   "TotalCost", [TotalCost]
)
Return Table1
 
Also, using the below measure in filter of table visual and set it to value 1.
 
Measure Filter =
VAR MinValue = MIN('Slicer Measure'[TotalCost])
VAR MaxValue = MAX('Slicer Measure'[TotalCost])
VAR CurrentMeasureValue = [TotalCost]
RETURN
IF(
    CurrentMeasureValue >= MinValue && CurrentMeasureValue <= MaxValue,
    1,
    0
)
 
when i apply single value from drop down slicer using "Slicer Measure", it filters the table correctly, but when select two or more values from the slicer it gives all the values in the table visual in between those selected values.
For expample, if i select 2 and 10 from the slicer then it shows all the values on the table visual in between 2 and 10 while it should show only 2 and 10 not the values which are in between.
Please help to correct my DAX.
 
Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Anonymous Please try this one:
    Slicer Measure = VAR Table1 = SUMMARIZE( Product, Product[ProductName], Product[ProductNumber], "TotalCost", [TotalCost] ) RETURN Table1


    Measure Filter =
    VAR SelectedTotalCosts = VALUES('Slicer Measure'[TotalCost])
    VAR CurrentMeasureValue = [TotalCost]
    RETURN
    IF( COUNTROWS
    ( FILTER
    ( SelectedTotalCosts, CurrentMeasureValue = [TotalCost] )
    ) > 0, 1, 0 )

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I have tried to change the "Measure Filter" like this:

    Measure Filter =
    VAR SlicerValue = SUMMARIZE('Slicer Measure','Slicer Measure'[TotalCost])
    VAR CurrentMeasureValue = [TotalCost]
    RETURN
    IF(
      CurrentMeasureValue in
     SlicerValue ,
        1,
        0
    )
    It gives me the expected result on the table visual but in the slicer there are few duplicated values. Can anyone suggest what need to do to remove these duplicate values in the slicer.
     
    Thanks
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Anonymous Please try this one:
    Slicer Measure = VAR Table1 = SUMMARIZE( Product, Product[ProductName], Product[ProductNumber], "TotalCost", [TotalCost] ) RETURN Table1


    Measure Filter =
    VAR SelectedTotalCosts = VALUES('Slicer Measure'[TotalCost])
    VAR CurrentMeasureValue = [TotalCost]
    RETURN
    IF( COUNTROWS
    ( FILTER
    ( SelectedTotalCosts, CurrentMeasureValue = [TotalCost] )
    ) > 0, 1, 0 )