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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mypowerbi1
Helper III
Helper III

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

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

mypowerbi1
Helper III
Helper III

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

@mypowerbi1 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.