Forum Discussion
Countif per alphanumeric Columns
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
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
3 Replies
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 GeraldesThank 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 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
