Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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.
 
4 REPLIES 4
Sandhya1234
Helper II
Helper II

@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
Not applicable

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
Sandhya1234
Helper II
Helper II

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.