Countif Contain Text from Other Column

[Seum]

I have a table of raw data, like the following:

Now I want to create a new DAX Measure to count the below table:

Please suggest new measure DAX Code or any other new process

[tamerj1]

Hi @Seum 

I guess these are not just letters, they can be any text with any number of letters? But they are always separated with comma?

[v-cazheng-msft · Accepted solution]

Hi @Seum,

You may try this solution.

1 Unpivot your table columns in Power Query Editor(Transform data) and remove unnecessary columns, then you will get a Product table

2 Create a Calculated column with the following dax formula

alphabet = SELECTCOLUMNS( GENERATESERIES(UNICODE("A"),UNICODE("Z")),"Character",UNICHAR([Value]))

Build one-many relationship for these two tables

3 Create a Measure to count the characters

COUNT =
VAR res =
    CALCULATE ( COUNT ( 'Table'[Product] ), VALUES ( 'Table'[Product] ) )
RETURN
    IF ( ISBLANK ( res ), 0, res )

Then, you will get the desired result.

Also, attached the sample pbix file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[daXtreme]

It's an easy task but you have to change your data model. In Power Query use the transformations to have one letter per row, no commas. Best transformations are split column on delimiter and then unpivot. This will give you the format of data you need.