Count *text string* match between 2 tables

[PBIsteven77]

I have looked through the suggested solutions, asked AI sources, and talked to peers, alas, i am still stumped.

I have a spreadsheet where the user has applied multi select, where -from a drop down menu the user is able to include more than one of the drop down options. In my testing example, "Obesity" has been chosen on 6 seperate rows and in a 7th row, "Obesity" and "Other neurological disorders" was chosen. When that cell is imported, it shows on the 'SLR-Append' table as "Obesity, Other Neurological disorders". 

I need a visual to show me that Obesity was chosen 7 times in total. This measure got me the closest to what i'm looking for but still only shows 6 on a visual. 

SUMX(

    'Elixhauser Variable Description',

    VAR CurrentVariable = "*"&'Elixhauser Variable Description'[Variable]&"*"

    --VAR CurrentVariable = "*Obesity*"

    RETURN

    CALCULATE(

        COUNTROWS(

            FILTER(

                'SLR-Appended',

                CONTAINSSTRING('SLR-Appended'[Elixhauser Variable Description (CD)], CurrentVariable)

            )

        )

    )

)

The wildcard appears to work on all non multi-select values (and i tested with more than just the obesity variable), but even when i try to search for *"Other"* - it is found on other optoins, (such as "Anemia with other ...") but the other from this multiselect cell is not added to the count.

Elixhauser Variable description table:

Variable

Obesity

Anemias with other complications

Other neurological disorders

SLR-Append Table:

Elixhauser Variable Description (CD)

Obesity

Obesity

Obesity

Obesity

Obesity

Obesity

Obesity, Other neurological disorders

Anemias with other complications

need to see a count of:

Obesity: 7

Other Neurological disorders: 1

Anemias with other complications: 1

I hope this is clear enough to explain the issue im having. Thank you

[danextian · Accepted solution]

Hi @PBIsteven77 

Try these measures:

Count of Instance = 
CALCULATE (
    COUNTROWS ( 'Description' ),
    FILTER (
        'Description',
        CONTAINSSTRING (
            'Description'[Elixhauser Variable Description (CD)],
            SELECTEDVALUE ( Variable[Variable] )
        )
    )
)

Count of Instance2 = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Variable, Variable[Variable] ),
        "@instance",
            CALCULATE (
                COUNTROWS ( 'Description' ),
                FILTER (
                    'Description',
                    CONTAINSSTRING (
                        'Description'[Elixhauser Variable Description (CD)],
                        [Variable]
                    )
                )
            )
    ),
    [@instance]
)

The first one returns the total matching rows from Description. The second one will return the total of individual rows.

[danextian · Accepted solution]

Hi @PBIsteven77 

Try these measures:

Count of Instance = 
CALCULATE (
    COUNTROWS ( 'Description' ),
    FILTER (
        'Description',
        CONTAINSSTRING (
            'Description'[Elixhauser Variable Description (CD)],
            SELECTEDVALUE ( Variable[Variable] )
        )
    )
)

Count of Instance2 = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Variable, Variable[Variable] ),
        "@instance",
            CALCULATE (
                COUNTROWS ( 'Description' ),
                FILTER (
                    'Description',
                    CONTAINSSTRING (
                        'Description'[Elixhauser Variable Description (CD)],
                        [Variable]
                    )
                )
            )
    ),
    [@instance]
)

The first one returns the total matching rows from Description. The second one will return the total of individual rows.

[PBIsteven77 · Accepted solution]

Thank you for these. They were also giving me the same result. So i disconnected the relationship with the 2 tables, and the issue went away. Bone-headed mistake on my part. Thank you for hte quick response!

[rajendraongole1]

Hi @PBIsteven77 - you can use the revised version of your measure that should work to count the individual selections from the multi-select field

CountVariableSelection =
SUMX(
'Elixhauser Variable Description',
VAR CurrentVariable = 'Elixhauser Variable Description'[Variable]
RETURN
CALCULATE(
COUNTROWS(
FILTER(
'SLR-Appended',
-- Split the values in the multi-select cell and check if the variable is contained in that list
ISNUMBER(SEARCH(CurrentVariable, 'SLR-Appended'[Elixhauser Variable Description (CD)], 1, 0))
)
)
)
)

[PBIsteven77]

Im getting exactly the same results in the matrix visual (_M.Occurance Count is my original measure):

[PBIsteven77]

Thank you for this. I disconnected the relationship with the 2 tables, and the issue went away. Bone-headed mistake on my part. Thank you for the quick response!