topic Re: Countif Contain Text from Other Column in DAX Commands and Tips

Countif Contain Text from Other Column

Seum — Fri, 03 Jun 2022 14:03:12 GMT

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

Re: Countif Contain Text from Other Column

daXtreme — Fri, 03 Jun 2022 17:28:02 GMT

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.

Re: Countif Contain Text from Other Column

v-cazheng-msft — Tue, 07 Jun 2022 07:01:26 GMT

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

Re: Countif Contain Text from Other Column

tamerj1 — Tue, 07 Jun 2022 12:16:07 GMT

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?