how to get rowcount based on text filter or if text containing the word "xyz" etc

[SantoshKumar]

 etcHi All,

I have a table and in that one column contains few values as below.
requirement :

i want get the count of rows with some conditions like below:

1. Count of row which contains only "first"

2.  Count of row which contains  "first" and "Second"

3. Count of row which contains "first" but not "Second" or "third" etc.

Sample column posted below:

Column

first;second;third;fourth;fifth;

first;second;third;fourth;fifth;

first;second;third;fourth;fifth;

first;second;third;fourth;fifth;

first;second;third;fourth;fifth;

first;second;third;fourth;fifth;

first;second;third;fourth;fifth;

first;second;third;fourth;fifth;

first;second;third;fourth;fifth;

first;second;third;fourth;

first;second;third;fourth;fifth;

first;second;third;fourth;fifth;

first;second;third;fourth;fifth;

first;second;

first;second;third;fourth;fifth;

first;second;third;fourth;fifth;

first;second;third;fourth;fifth;

second;third;fourth;fifth;

first;third;fourth;fifth;

first;fourth;fifth;

first;second;third;fifth;

second;third;fourth;fifth;

first;fourth;fifth;

first;second;

first;second;third;fifth;

first;second;fourth;fifth;

third;fourth;fifth;

fourth;fifth;

first;second;third;fourth;fifth;

first;second;third;fourth;fifth;

thanks

Santosh

[Gopal30]

To achieve this you first need to split the column for each value for example:

You can achieve this in Query Editor--> Transform(Toolbar) --> Split Column --> By Delimiter

I hope you are looking for this. 

Cheers!

[SantoshKumar]

@Gopal30thanks for your response but i want to have a measure to get the count as my table contains huge rows.

split doesnot make sense at this time.

[AkhilAshok · Accepted solution]

You could play with the new CONTAINSTRING function which was recently released:

First # = 
CALCULATE ( COUNTROWS ( 'Table' ), CONTAINSSTRING ( 'Table'[Column], "first" ) )

First & Second # =
//Use this if you expect second after first. If you want it irrespective of order, then do 2 CONTAINSSTRING check.
CALCULATE (
    COUNTROWS ( 'Table' ),
    CONTAINSSTRING ( 'Table'[Column], "first*second" )
)

First Not Second & Third # = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    CONTAINSSTRING ( 'Table'[Column], "first" ),
    NOT ( CONTAINSSTRING ( 'Table'[Column], "second" ) ),
    NOT ( CONTAINSSTRING ( 'Table'[Column], "third" ) )
)

[SantoshKumar]

@AkhilAshokThanks a Lot. This feature is awesome 🙂