cancel
Showing results for
Did you mean:
Frequent Visitor

## Count the occurrence of values in a column

Hi All,

I'm stuck trying to figure this out and was wandering if anyone could help guide me in the right direction.

I have 2 tables that are conntected by the UserID column, that is unique to Table 1. I also have Table 2 where actions are captured by UserID.

In Table 1, I need to create new columns based on actions and count actions per userID

Example:

1 ACCEPTED SOLUTION
Super User III

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
8 REPLIES 8
Community Support

Hi, @simlee

You can create three Calculated columns to get the result you want.

Eat Count = CALCULATE(COUNT(Actions[Action]),ALLEXCEPT(Users,Users[UserID]),Actions[Action]="Eat")

Sleep Count = CALCULATE(COUNT(Actions[Action]),ALLEXCEPT(Users,Users[UserID]),Actions[Action]="Sleep")

walk count = CALCULATE(COUNT(Actions[Action]),ALLEXCEPT(Users,Users[UserID]),Actions[Action]="walk")

The result looks like this:

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User III

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User I

Add these 3 Measures to your summary (Dim) Table.  Then drag them into a Table as Values

Walk = CALCULATE(COUNT('Fact-Table'[Action]), UPPER('Fact-Table'[Action]) = "WALK")
Sleep = CALCULATE(COUNT('Fact-Table'[Action]), UPPER('Fact-Table'[Action]) = "SLEEP")
Eat = CALCULATE(COUNT('Fact-Table'[Action]), UPPER('Fact-Table'[Action]) = "EAT")

Please give Kudos or Mark as a Solution!

Proud to be a Super User helping give back to the community!
Thank You!

Frequent Visitor

I'm lost trying to drag them into a Table as Values. it's shows me the total of each action in my Dim_user Table.

Thank you

Super User I

Do you have Dim_User as a Value as well in the table?  (Also, enable 'Show Items with No Data' under the User Dropdown in the Values area.)

Please give Kudos or Mark as a Solution!

Proud to be a Super User helping give back to the community!
Thank You!

Frequent Visitor

Actually most of my Dim_User s as list as text such as ABC12, ABC 133, ACB232....

Sorry for the confusion

Super User I

Shoulnd't matter, can you provide screen shots, or more details of you table structure?

Please give Kudos or Mark as a Solution!

Proud to be a Super User helping give back to the community!
Thank You!

Frequent Visitor

As you can see, I need to lookup and cout the actiosn of each User in table 2 and polulate table 1 for each user.

Announcements