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
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
