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.
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):
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
Solved! Go to 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
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 |
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
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
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 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |