Rows related by either of 2 fields

[Anonymous]

I have a table of customer records and am looking to filter by any single record and subsequently display all records with the same Name OR same SSN as that single record. 

Index	Name	TaxID
1	John Smith	333333333
2	John R. Smith	333333333
3	John Smith	null
4	Mary Johnson	444444444
5	M. Johnson	444444444
6	M. Johnson	222222222

And example for this dataset would be: If I select the first row, rows 1,2, and 3 would be displayed.

I do not believe this is possible using the Drillthrough feature, but I feel as if there may be some way to model the data and filter by a measure that could work.

Any help is appreciated, thank you!

[v-yulgu-msft]

Hi @Anonymous ,

Fisrtly, you should extract the last name from [Name] column in Query Editor mode.

Apply above changes. In report view, create an extra table as below which is unrelated to source table. You should add field from this new table into slicer.

Copy table = VALUES(Table1)

Add below measure into visual level filter and set its value to 1.

Measure =
IF (
    CALCULATE ( MAX ( 'Table1'[Name.2] ) )
        = CALCULATE (
            VALUES ( 'Table1'[Name.2] ),
            FILTER ( 'Table1', 'Table1'[Name.2] = SELECTEDVALUE ( 'Copy table'[Name.2] ) )
        ),
    1,
    0
)

Best regards,

Yuliana Gu

[Anonymous]

Thank you for the thorough answer! However, I am afraid I lead you astray in trying to simplify my sample data. I am still new to posting here. I am dealing with records whose Name filed could contain "THE CITY OF PHOENIX, AZ" or "COCA COLA" or "John and Mary Smith" as well as simple names. I have given a more fitting sample of an expected aggregation below.

Index	Name	TaxID
1	Coca-Cola	444444444
2	Coke LLC	444444444
3	Coca-Cola	222222222

Ultimately the only way to relate all three of these records, as I see it, is a variation on your original solution that filters for matching names OR matching TaxID. I just do not know how to implement that logic.