cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simlee
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:

4155.jpg

 

Thanks in advance.

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
v-cazheng-msft
Community Support
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:

v-cazheng-msft_0-1614575927995.png

 

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.

Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

fhill
Super User I
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")

fhill_0-1614370253768.png

 



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


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




simlee
Frequent Visitor

Thank you for your comment.

 

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.

 

Can you please guide me?

 

Thank you

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.)  



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


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




simlee
Frequent Visitor

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

Sorry for the confusion

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

 



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


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




simlee
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.

 

41030.jpg

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.