Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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
Super User

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/
fhill
Resident Rockstar
Resident Rockstar

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!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to 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

fhill
Resident Rockstar
Resident Rockstar

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!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to 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

fhill
Resident Rockstar
Resident Rockstar

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!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.