Data grouping with multiple entries in cells.

[mwjansen]

In 'Table' table there is a column [Body Part]. The data comes from user entries, and they are allowed to select multiple entries. 

ie Hand, Ankle, Back (the entry form inserts the commas/spaces). 

I want to be able to group (or count) each individual body part for use in a visual. 

            Body Part

Row     Hand (r), Hand (l), Shoulder (l)

            Ankle (r)

            Leg (l), Hand (r)

            Hand (l), Neck, Back

            Back, Shoulder (r)

In this example, I want to be able to show a count of Hand(4), Ankle (1), Leg(1), Neck(1), Back(2), Shoulder(2). 

Not concerned with L or R for this count. 

I tried data grouping, but once I put the first entry into a group (for Hand), I cannot use it in another group (for Shoulder), and thus the Shoulder count would be off. 

Is there another way? Some kind of measure I could write? 

[Ashish_Mathur]

Hi,

In Power Query, split data of that column into rows and remove the information within brackets.  Now you should be able to use this measure

Measure = countrows(Data)

Hope this helps.

[parry2k]

@mwjansen are you adding it as a column or a measure? 

[parry2k]

@mwjansen create a new table for body parts and use the following DAX. See attached file for reference:

Body Parts Count = 
VAR __Count = 
SUMX ( 
    'Table',
    VAR __CurrentRow =  SUBSTITUTE ( 'Table'[Row], "," , "|" )
    VAR __CurrentRowTotalBodyParts = PATHLENGTH ( __CurrentRow )
    VAR __CurrentBodyPartsTable = 
        SELECTCOLUMNS ( 
            ADDCOLUMNS ( 
                GENERATESERIES ( 1, __CurrentRowTotalBodyParts ), 
                "Current Part", PATHITEM ( __CurrentRow, [Value] ) 
            ), 
            "Current Part", [Current Part] 
        )
    VAR __BodyPartsFound =  
    FILTER ( 
        CROSSJOIN ( __CurrentBodyPartsTable, 'Body Parts' ),
        CONTAINSSTRING ( [Current Part], [Body Part] )
    )
    RETURN
       COUNTROWS ( __BodyPartsFound )
    
)
RETURN 
    __Count 



        

Result:

[mwjansen]

@parry2k I believe I made all the subs in the DAX. 

"The expression specified in the query is not a valid table expression"

Body Parts Count = 
VAR __Count = 
SUMX ( 
    'OR Incident Log',
    VAR __CurrentRow =  SUBSTITUTE ( 'OR Incident Log'[BODY PART], "," , "|" )
    VAR __CurrentRowTotalBodyParts = PATHLENGTH ( __CurrentRow )
    VAR __CurrentBodyPartsTable = 
        SELECTCOLUMNS ( 
            ADDCOLUMNS ( 
                GENERATESERIES ( 1, __CurrentRowTotalBodyParts ), 
                "Current Part", PATHITEM ( __CurrentRow, [Value] ) 
            ), 
            "Current Part", [Current Part] 
        )
    VAR __BodyPartsFound =  
    FILTER ( 
        CROSSJOIN ( __CurrentBodyPartsTable, 'Anatomy' ),
        CONTAINSSTRING ( [Current Part], [Body Part] )
    )
    RETURN
       COUNTROWS ( __BodyPartsFound )
    
)
RETURN 
    __Count 

"Current Part" is highlighted red 

[parry2k]

@mwjansen how many approx. rows you have in the transaction tables?

[mwjansen]

@parry2k 645 rows. 

Also, I want to exclude the (r) and (l) identifiers in the cells. 

[parry2k]

@mwjansen you need to create a table with all the body parts that you want to count. once that table is available, you can iterate thru each row value of that table and check with transaction table to get the count.

[mwjansen]

@parry2k 

I think I understand what you've written, but have no idea how to get started.