Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Generic Search Bar (for table visual)

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?

 

Ceejay_26_1-1639717686000.png

 

Thank you so much.

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@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.

jdbuchanan71_0-1639720289462.png

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.

2021-12-16_22-55-28.jpg

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

 

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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 

 

Ceejay_26_0-1641191968867.png

 

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
Not applicable

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

jdbuchanan71
Super User
Super User

@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.

jdbuchanan71_0-1639720289462.png

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.

2021-12-16_22-55-28.jpg

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

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors