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.
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!
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |