Countif per alphanumeric Columns

[flyingfox]

Hi, I am new to PowerBI and am trying to figure out how to implement this scenario with DAX.

I have a table containing customers ID with some attributes and for each customer is associated a number of tickets (a ticket in a different cell):

• Customer key
• Customer description...
• From Column n to Column (n+m) there is a Ticket number, starting with TT (e.g. TT001, TT099,etc)

For each row, it means for each customer, I would like to count the total number of Tickets.

I suppose to create a custom column containing the total number of  ticket, can anyone help me? In Excel I would have use a COUNTIF formula (filtering by LIKE "TT%"), it does not exist in Power BI. Thank you

[flyingfox]

Thank you Gonçalo for your help, but probably I miss something because it does not work. Let's call the table CUSTOMER; Col1, Col2, Col3....Coln are the columns containing a Ticket id (starting with TT). I would like to count the total number of Ticket for each customer.   Thank you

Customer ID	Company	Col1	Col2	Col3	Col4	Col5	Col6	......
ID1	Paul	TT001	TT002	TT005	TT006	TT021	TT201
ID2	John	TT304	TT413	TT521	TT601
ID3	Mark	TT114	TT116

[goncalogeraldes · Accepted solution]

Hello there @flyingfox ! Suppose you have a table like the one you shared, you can do the following:

1. Select the Customer and Company columns and right click on one of the two columns and select "Unpivot Other Columns"

2. You can remove the "Attribute" column and rename the "Value" column

3. Close and apply

4. Use the following count measure:

Ticket p/ customer = COUNTA('Table 1 (Sheet1)'[Ticket])

The final result will be this:

Here is the M code if you need it:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Book1.xlsx"), null, false),
    Sheet1_sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    FilterNullAndWhitespace = each List.Select(_, each _ <> null and (not (_ is text) or 
    Text.Trim(_) <> "")),
    #"Removed Bottom Rows" = Table.RemoveLastN(Sheet1_sheet, each try 
    List.IsEmpty(List.Skip(FilterNullAndWhitespace(Record.FieldValues(_)), 1)) otherwise 
    false),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Bottom Rows", 
    [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Company ", 
    "Customer ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Ticket"}})
in
    #"Renamed Columns"

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[goncalogeraldes]

Hello there @flyingfox ! Check if this works for you:

CountIf =
CALCULATE (
    COUNTA ( 'Table'[Ticket Number] ),
    FILTER ( 'Table', LEFT ( 'Table'[Ticket Number], 2 ) = "TT" )
)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes