Count all multiple text values in a column

[tt.xavier]

Hi All,

I need your assistance to sort out a little problem I have run into. I have a dataset with ID column and a Themelist column. Each ID could have one or multiple Themelists. Basically, I want to count every single themelist and plot it in a pie chart. Usually in PBI, I would have used a split by parameter, then unpivot the data and I would of got my visual. Also, I need to pick only certain themelists in my counts. The problem I have is, I am accessing a direct query from a data warehouse and I don’t have build permission to modify the dataset. So, is it possible to do this via dax measure instead?

Problem:

1. Count all themelists in one column per ID
2. Count only the themelists that starts with “XXX-” string in front of the themelist description (ie. XXX-Test1, AA-Test5)
3. Ignore all other themelist descriptions in the count

Solution:

I have attached a test data input file and the pbix file that shows the solution I need. Here is the visual I am trying to create by using dax measure instead.

Themelist.xlsx (15.5 KB)
Themelist.pbix (26.6 KB)

I will greatly appreciate if you can assist me to produce this solution by using a dax measure. Thank you.

[Harsh]

Hello @tt.xavier,

Thank You for posting your query onto the Forum.

To achieve the desired solution, just write the two measures as provided below -

Count of All Theme Lists = COUNTROWS( Data )

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

Since you’re using a “DirectQuery” mode, you can use “COUNTROWS()” function in a measure but not in a calculated column. Below is the link of the documentation provided.

I’m also attaching the working of the PBIX file for the reference purposes.

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

Thanks and Warm Regards,
Harsh

Themelist - Harsh.pbix (27.8 KB)

COUNTROWS function (DAX) - DAX

[Harsh]

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-" )

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.

Thanks and Warm Regards,
Harsh

Themelist - Harsh v2.pbix (24.2 KB)

[tt.xavier]

@Harsh you are genius mate. Thank you so much.

I managed to understand most of your measure except step 2. What are you trying to do there? I have many different themelist descriptions, do I need replace the test ones you have mentioned in step 2? Thanks again

[Harsh]

Hello @tt.xavier,

You’re Welcome!!!

I’m glad that I was able to assist you and thank you for your kind words of appreciation.

So the reason why I created an additional column in Step 2 is because when the new table is created in that under the field - “TAGS Split”, if you see there’re 7 distinct values whereas in actual sense there’re only 5.

It considered “AA-Test 5” and “AA-Test5” as well as “YYY-Test 2” and “YYY-Test2” as two different values just because of “Space” between them. Below is the screenshot provided for the reference -

And therefore, to consider them as one and the same I created that additional column so that we get 5 distinct values in the themelist section i.e., the way we wanted to see. Below is the screenshot provided for the reference -

Hoping this explanation solves your doubt about the “Step No. 2”.

Thanks and Warm Regards,
Harsh

[tt.xavier]

Thanks very much @Harsh. Have a great day