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? 

[mwjansen]

hello @v-pgoloju 

I followed the pbix/DAX from parry2k, but when i create the same DAX and table in mine, i get an error:

Calculation error in measure 'Body Parts'[Body Parts Count]: The arguments in GenerateSeries function cannot be blank. 

That's where I'm stuck. 

[v-pgoloju]

Hi @mwjansen,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Just following up to see where you're currently facing challenges in implementing the solution. Are you encountering issues with Power Query or DAX? If you've made any progress or tried a different approach, feel free to share it here we’re happy to assist further.

If a solution worked for you, kindly consider marking it as accepted and giving it a thumbs-up to help others in the community benefit as well.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @mwjansen,

Just a gentle reminder  has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[mwjansen]

hi @v-pgoloju , still working on a solution for this. 

[v-pgoloju]

Hi @mwjansen,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution  it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @mwjansen,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[parry2k]

@mwjansen not sure why, but you should follow what is in the attached pbix file. Not sure what else to tell you.

[mwjansen]

@parry2k I'll keep plugging at it. Thank you for your help. 

[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? 

[mwjansen]

@parry2k I was adding as a table

Modified to a measure, but I get no results. 

[parry2k · Accepted solution]

@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.