Forum Discussion
FILTER INQUIRY
Hello good evening.
What I need is something that I guess isn't difficult but since I'm new to this, I'm struggling.
I have an order ID, which can be repeated in several rows (according to the number of items that are from the same order):
I need to find all orders that contain a certain item "X", but I need to be able to view the entire order and not just that I have that item left. Example:
| ORDER ID | ITEM |
| 1 | mouse |
| 1 | keyboard |
| 1 | notebook |
| 2 | mouse |
| 3 | mouse |
| 3 | keyboard |
| 4 | notebook |
I need to show orders that contain the item "notebook". But not just show that row, but I need to show the 3 rows that are part of the order.
Can it be done through a visualization?
Could you create a new Boolean column, called "contains notebook" and put true or false to all rows of the same order?
One way I can think to do it is to build a table containing the unique items.
Items = DISTINCT ( 'Order Table'[ITEM] )This table is not connected to the order table, we just use it to populate the slicer and calculate the filter measure.
The filter measure is like this. We build a list of all orders that have the selected item(s) then return the count of order numbers. Basically, this will return a 1 for all the rows of the order in the _orders list.
Order Filter = VAR _Items = DISTINCT ( Items[ITEM] ) VAR _Orders = CALCULATETABLE ( DISTINCT ( 'Order Table'[ORDER ID] ), 'Order Table'[ITEM] IN ( _Items ) ) RETURN COUNTROWS ( _Orders )We add this measure to the table visual:
I have attached my sample file for you to look at.
1 Reply
One way I can think to do it is to build a table containing the unique items.
Items = DISTINCT ( 'Order Table'[ITEM] )This table is not connected to the order table, we just use it to populate the slicer and calculate the filter measure.
The filter measure is like this. We build a list of all orders that have the selected item(s) then return the count of order numbers. Basically, this will return a 1 for all the rows of the order in the _orders list.
Order Filter = VAR _Items = DISTINCT ( Items[ITEM] ) VAR _Orders = CALCULATETABLE ( DISTINCT ( 'Order Table'[ORDER ID] ), 'Order Table'[ITEM] IN ( _Items ) ) RETURN COUNTROWS ( _Orders )We add this measure to the table visual:
I have attached my sample file for you to look at.