Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
2 years ago
Solved

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 th...
  • jdbuchanan71's avatar
    jdbuchanan71
    2 years ago

    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.

     

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
2 years ago

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.