Forum Discussion
Filter text values in table A based on partial match from table B
Community ChampionHi mizotac
You're going to need to explain it better. I don't see anything from the ICD column in Diagnosis. How about an example based on the data you show, where you explain the steps and the expected result.
Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
AlB Thank you. Here are plain samples below in tabular format, the best I can do due to data confidentiality. Target end result, using a slicer on 'Diagnosis'; if user clicks on GERD, rows 2,3 & 6 from Table 1 will be retrieved. If user clicks on 'Cough', rows 5 & 7 will retrieved.
I hope this clarifies the situation. Thanks again.
| # | OrderNo | ICDCodesAll | Amount |
| 1 | 2135150 | ,S61.402A, | 57.75 |
| 2 | 2135254 | ,K21.9,R07.89,R10.10, | 145.17 |
| 3 | 2135901 | ,K21.9,M54.5,R25.2, | 176 |
| 4 | 2135918 | ,R52,H65.02,H92.02,R50.9, | 100.72 |
| 5 | 2135979 | ,J06.9,J30.9,K29.00,M54.5,M79.1,R50.9,R05, | 80.66 |
| 6 | 2135996 | ,K21.9,K59.00,R14.0, | 103 |
| 7 | 2136013 | ,H10.9,R51,R05, | 40.14 |
| ICD | Diagnosis |
| R05 | Cough |
| R06.00 | Dyspnea, unspecified |
| R06.01 | Orthopnea |
| R06.02 | Shortness of breath |
| K21.0 | GERD with esophagitis |
| K21.9 | GERD |
- AlB
Hi mizotac
One option, if you don't want to split the ICDCodes column in several ones as Matt suggested, is to create a measure for the visual filter:
1. Place all columns you want to show of Table1 in a table visual. Make sure all are set to "Don't summarize"
2. Table2[Diagnosis] in a slicer
3. Create this measure
ShowMeasure = VAR Selected_ = SELECTEDVALUE ( Table2[ICD] ) VAR Current_ = SELECTEDVALUE ( Table1[ICDCodesAll] ) RETURN IF ( SEARCH ( Selected_, Current_, 1, 0 ) > 0, 1, 0 )4. Place the measure in the visual filters and choose to show when result is 1
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
AlB Thanks alot, working smoothly as instructed. Can I be greedy to ask how to apply the same if end user selects multiple values in the slicer ?
- AlB
Try this:
ShowMeasureV2 = VAR Selected_ = DISTINCT ( Table2[ICD] ) VAR Current_ = SELECTEDVALUE ( Table1[ICDCodesAll] ) RETURN IF ( SUMX ( Selected_, INT ( SEARCH ( [ICD], Current_, 1, 0 ) > 0 ) ) > 0, 1, 0 )Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers