HELP - Counting multiple columns containing text

[Anonymous]

Hi, 

Thanks in advance for taking a look at this. Im sure its simple but no calclulations seem to be working for me. 

I have several columns labeled License.1, License.2, License.3 ect after splitting the cells by denominator (,). I am now wanting to create a new column for each license to perform a count. For example, i want to create a new column called .USMI. The data shows there are multiple examples per line so i would need the new column to show me a count of the number of .USMI per line. 

I can create the following count which displays the information correctly in the new column: 

.USMI =

COUNTROWS(

FILTER(

RELATEDTABLE('Hilda Test Power BI Data'),

'Hilda Test Power BI Data'[License.1] = ".USMI"

))

The issue i am having is that i need this value to be a count be across all of the license columns but every all i get is error when i try to combine the columns in the query. As an example i have used: 

.USMI =

COUNTROWS(

FILTER(

RELATEDTABLE('Hilda Test Power BI Data'),

'Hilda Test Power BI Data'[License.1] = ".USMI" + 'Hilda Test Power BI Data'[License.2] = ".USMI" + 'Hilda Test Power BI Data'[License.3] = ".USMI"

))

Each attempt either does not pull through the correct data, or i get an error. This would have been simple in excel however struggling to translate this to Power BI. Any help would be hugely appriciated. 

Thanks, 
James

[v-yalanwu-msft]

Hi, @Anonymous ;

You could create a column as follow:

.USMI = 
COUNTROWS(
FILTER(
RELATEDTABLE('Hilda Test Power BI Data'),
'Hilda Test Power BI Data'[License.1] = ".USMI"))+COUNTROWS(
FILTER(
RELATEDTABLE('Hilda Test Power BI Data'),
'Hilda Test Power BI Data'[License.2] = ".USMI"))+COUNTROWS(
FILTER(
RELATEDTABLE('Hilda Test Power BI Data'),
'Hilda Test Power BI Data'[License.3] = ".USMI"))

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You need use and or OR ?

.USMI =
COUNTROWS(
FILTER(
RELATEDTABLE('Hilda Test Power BI Data'),
'Hilda Test Power BI Data'[License.1] = ".USMI" &&  'Hilda Test Power BI Data'[License.2] = ".USMI" &&  'Hilda Test Power BI Data'[License.3] = ".USMI"
))