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

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!