Generic Search Bar (for table visual)

[Anonymous]

Hello everyone,

Question.

I have this table where there are multiple Manufacturer Part Numbers. Currently I have a search bar for MPN 1 (Manufacturer Part Number 1). Is there a way to have a search bar that can search an MPN, even if it is in column MPN 1, MPN 2, MPN 3?

Thank you so much.

[Anonymous]

Hello @jdbuchanan71 thanks for the solution! I believe it can be used in other situations 🙂

Sorry, it's my fault for not clarifying. What I meant is that there are part numbers that are unique to a column. For example 

Murata's GRM219R71H333KA01D can only be found on MPN 2.

Hope it clears my problem.

But, thank you for the effort! I will definitely use the one you mentioned in one of my BI's 🙂

[Anonymous]

I'll try this later, and get back to you once it works. Thanks!

[jdbuchanan71 · Accepted solution]

@Anonymous 

One way I can think to do it would be to have a table that has all the unique part numbers in it.  You can build that from your existing data with a calcualted table.

Part Numbers = 
DISTINCT (
    UNION (
        DISTINCT ('Table'[Part Number 1] ),
        DISTINCT ('Table'[Part Number 2] ),
        DISTINCT ('Table'[Part Number 3] )
    )
)

Then you link that table to each of the part number fields in your main table.  Only one relationship can be active but we will handle that in the measure.

Then we add a measure that does a count of all the rows for the selected part across all the relationships.

Part Number Filter = 
VAR _P1 = COUNTROWS ('Table')
VAR _P2 = CALCULATE(COUNTROWS('Table'),USERELATIONSHIP('Part Numbers'[Part Number],'Table'[Part Number 2]))
VAR _P3 = CALCULATE(COUNTROWS('Table'),USERELATIONSHIP('Part Numbers'[Part Number],'Table'[Part Number 3]))
RETURN _P1 + _P2 + _P3

When we add the measure to a table and select a part number from the 'Part Numbers' table it will only return rows for order with the selected part number in atleast one of the main tables part number fields.

I have attached my sample file for you to look at.