Forum Discussion
Filter text values in table A based on partial match from table B
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
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
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 |
Community ChampionHi 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