Forum Discussion
Counting log messages
I've got a table that consists of three columns; an ID, a DateTime and a LogMessage. There are +/- 20k rows in the table. The log message are less than a dozen different types several kinds of error messages, some operations messages and some warnings. All messages are unique as they ususally have a username in the message but each type has a set format. A few examples below:
"Error, User 123456JohnSnow wasn't able to synch"
"User 123456SansaStark was not found in the AD synch from application"
"User 123456BobbyB is inactive according to table, remove from AD"
I need to count the total amount of messages of a certain type over a period of time. For example: The "User not found in AD sync from application" was found 330 times past month Or "Error, User wasn't able to sync" was found 3 times past year.
I'm new to PowerBI and would love for somebody to point me in the right direction.
Thanks in advance.
Hi JandeMul,
For example, using the DAX functions SWITCH and CONTAINSSTRING and the wildcard character *, you can assign types to errors and then count them.
DAX calculated column in plain text:
Column = SWITCH ( TRUE(), CONTAINSSTRING ( [Message], "Error, User * wasn't able to synch" ), "Type1", CONTAINSSTRING ( [Message], "User * was not found in the AD synch from application" ), "Type2", CONTAINSSTRING ( [Message], "User * is inactive according to table, remove from AD" ), "Type3", "Other" )Best Regards,
Alexander
1 Reply
Hi JandeMul,
For example, using the DAX functions SWITCH and CONTAINSSTRING and the wildcard character *, you can assign types to errors and then count them.
DAX calculated column in plain text:
Column = SWITCH ( TRUE(), CONTAINSSTRING ( [Message], "Error, User * wasn't able to synch" ), "Type1", CONTAINSSTRING ( [Message], "User * was not found in the AD synch from application" ), "Type2", CONTAINSSTRING ( [Message], "User * is inactive according to table, remove from AD" ), "Type3", "Other" )Best Regards,
Alexander