Count distinct records if string is contained in a column

[Anonymous]

I am working on a project for a client that they feel is going to be an integral part of their reporting moving forward. I essentially want to count the number of times a string appears in one column, summarized by a second column. I think it is going to be easiest to provide an example. The [ICD-10] column is essentially the primary code for each transaction. The [All ICD-10] column represents all other codes relevant to the transaction, separated by a space. This column was actually initially was separate by commas but I removed them because in my mind I thought it would be easier (so if having a column is actually necessary, then I can remove the step in Power Query that removed them). I want to be able to report on each primary [ICD-10] code and indicate how many times every other code appeared as a secondary code in [All ICD-10] column. For example, if I select "I10" as the primary code I want to evaluate in the below table, I should see that "E559" appeared as a secondary code 4 times out of the 5 transactions. I already have a measure that counts unique transactions called [Number of Encounters]. So I want to identify [Number of Encounters] that CONTAIN every other ICD-10 code. I am assuming this can be done because it is essentially what the Word Cloud visual does. I just haven't been able to execute a measure that is going to return the actual values so that I can display them in a different format (probably a bar chart). Any help would be greatly appreciated! 

ICD-10	All ICD-10
I10	E119 E538 D569 K589 E785  F99 F329 F1010 F0390 R451 D649 F29
I10	A809 Z951  E559 R5381 D649 K5900 R600 I771 I25810
I10	E559 J449 E785 K219 F0390 G20 F319 I739 I2510  M9909 M25512 B356 E119 R300 N390
I10	J449 I4891 G629 E559 F17210 K219 E119 R609 R531 J849  I509 K5900 W19XXXA B349 U071 J9610 L309 Z7289
I10	I208 E559 F419 E785 J449 J9610 F209 G2401 K219 G40909 I2510 H04129 R682 R05 J441 F410 R42 R062 F4321 R0600 R109 F22 K121 R251 F430

[harshnathani]

HI @Anonymous ,

You will need to create a mapping table. The Mapping table contains the Primary Code and the value to be searched.

You can then use the below DAX Code.

COUNTVALs =

var _t = SELECTEDVALUE(MappingTable[SearchValue])

RETURN

COUNTROWS(filter('Main Table', 'Main Table'[Primary Code] = SELECTEDVALUE('MappingTable'[Code]) && SEARCH(_t,'Main Table'[Secondary Code],,0)))

Mapping TableMain TableOutput

[amitchandak]

@Anonymous , a measure like

countrows(filter(Table, Table[primary code] ="I10" && search("E559",table[secondary code],,0)>0))

[Anonymous]

@amitchandak Thanks for responding! Can this be dynamic as well? That's what is really causing my issue. I need to do this evaluation for 100,000 different codes. So I need the search to search for all 100,000. And I need to be able to select one ICD code (i.e. I10) and know how many times each of the 100,000 codes appear in the transaction with I10 as the primary code. But the primary code also needs to be dynamic so we can select any of the 100,000 codes and get the same analysis. 

[ryan_mayu]

@Anonymous 

Please see the attachment below

[Anonymous]

@ryan_mayu This definitely looks like it is working, thanks for sending along! It looks like you split the All ICD-10 column and then unpivoted on the resulting columns? I was curious if there was a solution in DAX specifically just because I am worried that unpivoting will cause a significant increase in the dataset size and lead to poor performance. But if there are no options in DAX, then I will definitely use this solution. Thanks!

[ryan_mayu · Accepted solution]

@Anonymous 

I think then you need to create a code table.please see the attachment below

[Anonymous]

This did work, unfortunately with the volume of data the measure is calculating, the process and rendering time isn't acceptable for the client. We have 100,000 distinct ICD-10 codes that it has to search through and a few hundred thousand records in the transaction table. What we ended up doing is just splitting and then unpivoting the "Secondary" column. Unfortunately resulted in a significantly larger amount of records but I think it is our best bet. Thanks everyone for your help!