Forum Discussion
Countif Contain Text from Other Column
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
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
3 Replies
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.
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
