Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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" ) ) )
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!
@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.
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" ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |