Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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.
Solved! Go to 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.
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 🙂
I'll try this later, and get back to you once it works. Thanks!
@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.