Forum Discussion
Filter text values in table A based on partial match from table B
Hello fellow dax-ians ;)
I'm stuck with the following trick. In the below screenshot, I'd like to add a slicer from Table 2 column B (Diagnosis), so that end user will be able to filter paid amounts from Table 1 where ICD code is partially available in column B.
Of course both tables cannot be related. I tried with FILTER/IN VALUES and also with CONTAINS but no luck, also INTERSECT will not apply in this case.
Would appreciate your guidance. Thank you ..
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
8 Replies
- AlB
Community Champion
Hi 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
- MattAllington
What 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 ..
