filtering dataset for new visual

[Anonymous]

Hi,

Not sure if this has been answered, this is a little hard to describe.  I have a set of data with customer projects and electronic component part numbers registered to a project.  Some projects have multiple component parts registered with it; these are their own rows in the data set, but it all ties back together through a registration number.  So a registration is used multiple times in the data where different components are tied to a project.  Initially, I want to see where we have a specific component (key component) part number is registered within the customer projects.  But then, I want to be able to see the total list of components that are registered across of these projects that use that key component.  So I need to find registration numbers based on the key component then the list of all components associated with that registration....and then sales dollars assocated with that project/registration overall.  

Here is an example data set.  If the key component is partNumber abc123, I would see two registrations in this data set.  But if I wanted to see the total partNumbers associated with projects that use abc123, I would look that up based on the two registrations of 12345 and 56789.

Customer Name	Project Name	partNumber	registration
Customer 1	Project 1	abc123	12345
Customer 1	Project 1	abc345	12345
Customer 1	Project 1	abc567	12345
Customer 1	Project 1	abc789	12345
Customer 2	Project 2	zed123	98765
Customer 2	Project 2	zed234	98765
Customer 3	Project 3	abc123	56789
Customer 3	Project 3	asd007	56789

How can I go about creating that more complex visualization?  (At least to me!)

Perfecto

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may create a slicer table as below.Then you may create a measure and use it in visual level filter to filter the table.Attached sample file.

Table = DISTINCT(Table2[partNumber])

Measure =
VAR _table =
    CALCULATETABLE (
        VALUES ( Table2[registration] ),
        FILTER ( ALL ( Table2 ), Table2[partNumber] IN VALUES ( 'Table'[partNumber] ) )
    )
RETURN
    IF ( MAX ( Table2[registration] ) IN _table, 1 )

Regards,

Cherie