Forum Discussion

PBIsteven77's avatar
PBIsteven77
Regular Visitor
1 year ago
Solved

Count *text string* match between 2 tables

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

Need Help
  • danextian's avatar
    danextian
    1 year ago

    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's avatar
    PBIsteven77
    1 year ago

    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!

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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's avatar
      PBIsteven77
      Regular Visitor
      1 year ago

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

       

      • PBIsteven77's avatar
        PBIsteven77
        Regular Visitor
        1 year ago

        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!

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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's avatar
      PBIsteven77
      Regular Visitor
      1 year ago

      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!