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 ChampionWhat does “partially available” mean? I don’t see any similarities between the columns. Assuming they were similar, I would unpivot the column b into multiple rows, one per code. Create a bridging table containing all the codes between the 2 tables you have, then turn on bidirectional filtering from table 2 to the bridge table.
MattAllington Thank you, Sir. I just posted another clear example. Table 1 is a Facts table that grows to millions of rows. I thought of separating values into columns, but it increases file size and slows down the model. Table 2 'refrence table' is a fixed list of about 75k ICD's.
Hope that clarifies the situation.
Regards ..