Measure to exclude certain values from slicer

DAX DAX Calculations
1

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
A
B
C

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…

regards

2

Hi @sanappi09,

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

image

Result:

image

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

3

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
A
B
C

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
4

Hi @sanappi09,

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

Please find below:

When item A is selected:

image

When B is selected:

image

When C is selected:

image

Formulas:

LastOrderDate-A =

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


LastOrderDate-slicer =

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

eDNA-remove filters.pbix (91.4 KB)

5

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)

image

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

image

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

appreciate your help!

6

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.

7

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

slicer - itemnumber object
image

Itemnumber in Table object
image

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

regards

8

Hi @sanappi09,

Ok, then please try this:

Datev2 =

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

thanks Hafiz! this measure worked for my requirement…

10

That’s great :slight_smile: