Filter text values in table A based on partial match from table B

[mizotac]

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

[AlB · Accepted solution]

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. 

[mizotac]

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

[AlB]

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

[mizotac]

@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 · Accepted solution]

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  

[mizotac]

@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 · Accepted solution]

@mizotac 

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  

[mizotac]

Thanks again @AlB . However, the last code runs very slowly, I believe because SUMX has to iterate through large number of rows.

I will try to force my end users into the first scenario then.

Thank you so much. Really appreciated.

Moaaz