Measure to exclude certain values from slicer

Hello all, i could use some help from the experts here… i have a report with a slicer on item dimension, what i need to show in a table on this report, is to display the ‘last order date’ of this item along with the selected item’s sales/order date information.

Item Slicer

When A is selected

customername, ‘A’ last order date, Last Order Date, Sales
XYZ , 01/01/2020, 01/01/2020, $200

When B is selected

CustomerName, ‘A’ last order date, Last Order Date, Sales
XYZ , 01/01/2020, 01/31/2020, $500

Thanks in Advance…


Hi @sanappi09,

Seems easy. You can try below. I have created sample sales table with 3 items.




Please attached sample file.eDNA-remove filters.pbix (89.7 KB)

Hello Hafiz, thanks for the quick response…
i have a slicer in my dashboard on item, users can select an item from the slicer, the table should show the selected item details along with Item-A’s last order date…

Item Slicer

Sample Sales Table -

Item, Date, Amount
A, 01/01/2020, $200
B, 01/31/2020, $500
C, 02/01/2020, $800

When A is selected, the table should have the following values

          Customer,     ‘A’ last order date,  Last Order Date,  Sales
           XYZ,             01/01/2020,           01/01/2020,         $200

When B is from slicer selected, here’s how the table will look like

          Customer,     ‘A’ last order date,  Last Order Date,  Sales
           CDF ,             01/01/2020,           01/31/2020,         $500

Hope this is clear now…

When C is from slicer selected, here’s how the table will look like

          Customer,  ‘A’ last Order Date,  Last Order Date,  Sales
           FCD ,             01/01/2020,         02/01/2020,         $500

Hi @sanappi09,

Thanks for clarifying. Most difficult part for me is to understand requirements :frowning:

Please find below:

When item A is selected:


When B is selected:


When C is selected:



LastOrderDate-A =

CALCULATE ( MAX ( 'Sales'[OrderDate] ), 'Sales'[Item] = "A" )

LastOrderDate-slicer =

VAR selecteditem =
    SELECTEDVALUE ( 'Sales'[Item] )
    CALCULATE ( MAX ( 'Sales'[OrderDate] ), 'Sales'[Item] = selecteditem )

eDNA-remove filters.pbix (91.4 KB)

Hafiz, tried to same kind of measure , but its not working in my report…

as you can see from screenshot below, when i dont select an item from slicer, the measure is working fine(it is pulling in the last order date of a specific - ‘ESPX’ product)


But as soon as i select an item from slicer, this measure value disappears.


Measure - Comparison Product is defined as
Datev2 = CALCULATE(MAX(FactSales[Date]), DimItem[ItemNumber] = “ESPX5”)

appreciate your help!

Hi @sanappi09,

I don’t think that [Item Number] in slicer and DimItem[ItemNumber] are coming from same table. Are they? If both are coming from same table, DimItem[ItemNumber] = “ESPX5” will remove filter which is applied from slicer and apply its own filter. In this case, it is not removing but adding filter. So, comprision product is empty because it can not find max date where item number = “ESPX5” && item number = “ES9000”, so naturally it will return empty.

Please use same column from same table in slicer and in table for ItemNumber. If it does not work, I would appreciate if you can send me your pbix file.

Hi Hafiz, confirming that item number in slicer and table are from the same table…

slicer - itemnumber object

Itemnumber in Table object

There is a sale record for item ‘ESPX’ - highlighted in the image, we can see when i clear slicer selection -


Hi @sanappi09,

Ok, then please try this:

Datev2 =

    MAX ( FactSales[Date] ),
    FILTER ( ALL ( DimItem ), DimItem[ItemNumber] = "ESPX5" )

thanks Hafiz! this measure worked for my requirement…

That’s great :slight_smile: