Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

HELP - Counting multiple columns containing text

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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"
    ))

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.