Count all multiple text values in a column

DAX DAX Calculations
3

Hello @tt.xavier,

Sorry, I didn’t checked the Excel file and the way the original data actually is. So here’s the revised solution. Below are the steps provided as follows -

1). Create a Calculated Table -

Table = 
VAR ToPaths =
    ADDCOLUMNS (
        SELECTCOLUMNS (
            Data,
            "@ID", Data[ID],
            "@Path", SUBSTITUTE ( Data[Themelist], ",", "|" )
        ),
        "@Length", PATHLENGTH ( [@Path] )
    )
VAR T =
    ADDCOLUMNS (
        ToPaths,
        "@Cumulative", SUMX ( FILTER ( ToPaths, [@ID] <= EARLIER ( [@ID] ) ), [@Length] )
    )
RETURN
    ADDCOLUMNS (
        SELECTCOLUMNS (
            ADDCOLUMNS (
                GENERATESERIES ( 1, SUMX ( T, [@Length] ) ),
                "Cumulative", MINX ( FILTER ( T, [@Cumulative] >= [Value] ), [@Cumulative] )
            ),
            "Case Number", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [@ID] ),
            "TAGS", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [@Path] ),
            "Tag Number", 1 + [Cumulative] - [Value]
        ),
        "TAGS Split", PATHITEM ( [TAGS], [Tag Number] )
    )

2). Create a calculated column inside the calculated table -

Theme List = 
SWITCH( TRUE() , 
    'Table'[TAGS Split] = "YYY-Test 2" , "YYY-Test2" , 
    'Table'[TAGS Split] = "AA-Test 5" , "AA-Test5" , 
'Table'[TAGS Split] )

3). Now, create the measures that were provided in the previous post -

i). Count of All Theme Lists -

Count of All Theme Lists = COUNTROWS( 'Table' )

ii). Count of Theme List starts with “XXX-” -

Count of Theme List starts with "XXX-" = 
CALCULATE( COUNTROWS( 'Table' ) ,
    LEFT( 'Table'[Theme List] , 4 ) = "XXX-" )

Final Results
Final Results1403×819 28.6 KB

I’m also attaching the revised PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Themelist - Harsh v2.pbix (24.2 KB)

2 Likes