Forum Discussion
How to filter a table by a measure value
Hello,
I have a table of this kind:
ItemID On stock QTY
I1 0
I2 15
I3 5
I4 0
"On stock QTY" is a measure:
On stock QTY =
CALCULATE(
SUM(
'Inventory transactions'[QTY]),
ALL(DateTable[Date])
)
I'd like to have a filter like this one:
"=0"
">0"
to be able to filter out the rows with zero "On stock QTY" values.
I know that it's possible to use the filter, appearing when I click on the visual:
But I'd like to have a filter on the report page.
Could anyone suggest how to do it?
Hi Anonymous
follow the following steps
Create a calculated table
StockFilter = DATATABLE( "FilterOption", STRING, { {"= 0"}, {"> 0"} } )Create a measure to capture the selected filter
SelectedStockFilter = SELECTEDVALUE(StockFilter[FilterOption])Create a new measure to apply the filtering logic
ShowItem = VAR StockQty = [On stock QTY] VAR FilterOption = [SelectedStockFilter] RETURN SWITCH( FilterOption, "= 0", IF(StockQty = 0, 1, 0), "> 0", IF(StockQty > 0, 1, 0), 1 // Show all by default )now drag and drop this measure to filters and set showitem is 1
create the filter using the filter option column you create from the new calculated table
it should work !
1 Reply
- kushanNa
Super User
Hi Anonymous
follow the following steps
Create a calculated table
StockFilter = DATATABLE( "FilterOption", STRING, { {"= 0"}, {"> 0"} } )Create a measure to capture the selected filter
SelectedStockFilter = SELECTEDVALUE(StockFilter[FilterOption])Create a new measure to apply the filtering logic
ShowItem = VAR StockQty = [On stock QTY] VAR FilterOption = [SelectedStockFilter] RETURN SWITCH( FilterOption, "= 0", IF(StockQty = 0, 1, 0), "> 0", IF(StockQty > 0, 1, 0), 1 // Show all by default )now drag and drop this measure to filters and set showitem is 1
create the filter using the filter option column you create from the new calculated table
it should work !