Counting log messages

[JandeMul]

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.

[barritown · Accepted solution]

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

My YouTube vlog in English

My YouTube vlog in Russian