Count the number of rows containing specific text

[LeighMacKay]

I'd like some advice more than anything about how to tackle something which has simple logic in my mind. But I don't have the skillset or experience to be able to work it out.

I've got a dataset called activity_log which contains a field called description. The field called description is text which contains multiple different types of data 'Activated Account' being the one I am focussing my interests on.

I'd like to create an Advance Card which counts the number of times 'Activated Account' appears in the description field.

I've tried duplicating the column and replacing the text 'Activated Account' with 1 (So I can do a count) However, I don't know how to cleanse the rest of the options in the list (because their is a lot of other alternative options and new options could be added in the future).

Any help or advice would be greatly appreciated.

[themistoklis · Accepted solution]

@LeighMacKay

You forgot a closing bracket:

Measure= CALCULATE( COUNT('eventminder_a_trinity activity_log'[description]), 'eventminder_a_trinity activity_log'[description] = "Activated Account" )

[AlB]

Hi @LeighMacKay

I'm not sure I understand fully what you need but how about you use a simple measure like:

Measure=COUNT('Activity Log'[Description])

place the measure in a card and place the same Description field on a slicer, where you can dynamically select which one you want to count, "Activated account" or whichever you want.

[LeighMacKay]

Sorry I am quite new to the software so my explanations are probably not up to scratch.

Firstly, thank you for your reply.  

The developer in the company has basically created our own piece of Analytical software and I have been tasked with creating an overview report which shows how many people have activated their account and when.  The field 'description' has an option called 'Activated Account'.  It also has multiple different other types on..

I can do this in the table.  By adding a visual level filter.

But when I want to display the count of those selected (as shown in the example below.  I can't use the filters.  They just stop working.

 

[themistoklis]

@LeighMacKay

Use a filter to filter based on the description you want. The formula can look like this:

Measure= CALCULATE( COUNT('Table'[Description], 'Table'[Description] = "Activated Account" )

[LeighMacKay]

What you have suggested is really close to what I want aas it contains the correct logic.  Thank you.  

Just can't get the last part to work.  [description] is greyed out. 

Measure= CALCULATE( COUNT('eventminder_a_trinity activity_log'[description], 'eventminder_a_trinity activity_log'[description] = "Activated Account" )

[themistoklis · Accepted solution]

@LeighMacKay

You forgot a closing bracket:

Measure= CALCULATE( COUNT('eventminder_a_trinity activity_log'[description]), 'eventminder_a_trinity activity_log'[description] = "Activated Account" )

[LeighMacKay]

I also have another question.

There is a difference between and activation and a registration.

An activation is to confirm somebody has received their login credentials and a registration is to say that they have registered on their own accord.

The only difference between both tables is the 'created_at' date field.  For registrations it appears in the 'edge_laravel_users' table and in the activations it appears in the 'activity_log' table.

How wouuld I add this additional filter to the measure?

Activations_Count = CALCULATE( COUNT('eventminder_a_trinity activity_log'[description]), 'eventminder_a_trinity activity_log'[description] = "Activated Account" )

[themistoklis]

@LeighMacKay

what do you actually want to put in the filter, the created date?

Also if you could share the worksspace that would be great. Mask any sensitive data

[LeighMacKay]

In the registrations count measure which comes from the edge_laravel_users table, i'd like it to show the created_date IS NOT NULL (as a count) and only counts those which are called 'Account Activation' from the description table.

In the activations count measure which comes from the activity_log table, i'd like it to show the email_verified_by IS NOT NULL (as a count) and only counts those which are called 'Account Activation' from the description table.

I hope that makes sense?

[themistoklis · Accepted solution]

@LeighMacKay

Can you try these formulas and see if they work?

Activations_Count = CALCULATE( COUNT('eventminder_a_trinity activity_log'[description]), 
                                     'eventminder_a_trinity activity_log'[description] = "Activated Account",
                                     'eventminder_a_trinity activity_log'[email_verified_by] <> BLANK()								 
							 )
							 
OR

Activations_Count = CALCULATE( COUNT('eventminder_a_trinity activity_log'[description]), 
                                     'eventminder_a_trinity activity_log'[description] = "Activated Account",
                                     LEN('eventminder_a_trinity activity_log'[email_verified_by]) = 0 									 
							 )	

[LeighMacKay]

Absolute Super Star.

Thank you to all who contributed towards me getting the answer I wanted.  Especially having had a poor explanation in the first instance as to what I was trying to achieve.