community.fabric.microsoft.com
Open in
urlscan Pro
2600:141b:1c00:258a::3ac3
Public Scan
URL:
https://community.fabric.microsoft.com/t5/Desktop/Create-column-with-count-of-entries-by-ID/td-p/2275396
Submission: On May 23 via manual from US — Scanned from US
Submission: On May 23 via manual from US — Scanned from US
Form analysis 1 forms found in the DOM
Name: form — POST https://community.fabric.microsoft.com/t5/forums/v5/forumtopicpage.searchformv32.form.form
<form enctype="multipart/form-data" class="lia-form lia-form-inline SearchForm" action="https://community.fabric.microsoft.com/t5/forums/v5/forumtopicpage.searchformv32.form.form" method="post" id="form" name="form">
<div class="t-invisible"><input value="board-id/power-bi-designer/thread-id/825757" name="t:ac" type="hidden"><input value="search/contributions/page" name="t:cp" type="hidden"><input
value="2AllaQ5kLiBaEpT6K98rhESSnHZQYu0BO72xDkckbA1-onhmHyBvVcDe9O9Pys2I-b9PJu-V5k_HXOyPGz4wAkf00g2irNprqhz7mDYKHNaDsA04X6VHnmwadDGqRDSFgmB2zUByC0jJfYv8PwP-F0sI50p9jJnhyZbkBuS4BW9sXId804caU86xLkZkK73L_VV0aYpl8tCqsrd42tleaIzmCWP-Lqrjb3pmED1NO3b3nnoqxrAiGa6ea0B-ny0vkm29U51vZn3M8xFHTXkXvG_Em3TbzhsNaPZ8gmrTXw4CqzpavgPPPvtlS8uxCC9T5rTBdqClIkhg6iXcKeupNViR-cF0jhO6B_hO_q09Rt5avIPwiFfoGp6Ai6LrFl273XWVsc7-Q7EaTAMhOoBh3497V6eaODXLefhxiaNGqNpizSNVaLqjsonthRuOBIluvQTWZgwL1kiOfTCPo-cYI6O0sFddIRwPJeCai3FVYgoHZ4ue9KebBCzlk4Q3AIlEmIGMWasI5QyX3C6jJqSaUv_nTKUM8G49Uun0c-8U3vTLPRoTmOoRUb4DxG9XBrTp1X-4czv8GLBMK2X45MgAD0E-gG3Oc1m60Rsa2TKteoaE4Tain-CQKNwoJS4241nlrF93GYuipV1fX2zr8qbEjjuzX9p1470OqHW1LCfCrIIdzXk8ZE8lp0sXQ0DcHUkNYq6fJ3aAJVu0XtAqsyFBGcn8SjBhFhlb2Oua2A5dWg1KtGp7pAi1MLMSNsPPIJSskYmwikUngMUkuQgfwYgzR6yNIThztsAVg5HJB9RG1W_ZvtBGVN7SYNZyLLD1m7VqnWup-kL-liV8YAbprZLqs6iChs88DduJJfU44RtndDzoz_kUsuAGsMriHc3xk-iaqn6PfOMxqVLvqYYcgoaK-O4LFrkm5j-rpCS98p595jpQ3r3nByJm2S6m7agGHRjH0BaQU4ETDQ0jL5E3o4Yxlp6uKmlRmK79Xs4IhjWYqAtCPz0JZGq_T8axM4WV7P-Zc1mVKH8QB9HMrDTIcMLQLQ.."
name="lia-form-context" type="hidden"><input value="ForumTopicPage:board-id/power-bi-designer/thread-id/825757:searchformv32.form:" name="liaFormContentKey" type="hidden"><input
value="5DI9GWMef1Esyz275vuiiOExwpQ=:H4sIAAAAAAAAALVSTU7CQBR+krAixkj0BrptjcpCMSbERGKCSmxcm+kwlGrbqTOvFDYexRMYL8HCnXfwAG5dubDtFKxgYgu4mrzvm3w/M+/pHcphHQ4kI4L2dMo9FLYZoM09qbeJxQ4V0+XC7e/tamqyBPEChwgbh1JAjQtLIz6hPaYh8ZlEMaxplAvm2KZmEsm0hhmBhOKpzZzOlsEw8LevR5W3zZfPEqy0oJIYc+eCuAyh2rolfaI7xLN0I8rjWfWBj7CuzJvf5osmbxRN3hacMimNwHRtKSOr0XNnv/vx+FoCGPjhMRzljhNLYHrEt9kA5T08ACCsKvREoYuqxqLl8BLO84q4UcMITcG49y/QOGs1pYyESl5p6V6qwRW086rinVmoxMZsiZud/zBUTc6gmVc4kExkJafmcYG1GM9+wfIsCkf2OP54hal5EjnG54z8h0XhjfcF7wQUs5Kz0GTjU2rOjc/llTT4Au07pDOcBQAA"
name="t:formdata" type="hidden"></div>
<div class="lia-inline-ajax-feedback">
<div class="AjaxFeedback" id="feedback"></div>
</div>
<input value="G_qjV9ASrBAhGJvO6Ijvme0glY6G5nEYQm0Iit3PLJQ." name="lia-action-token" type="hidden">
<input value="form" id="form_UIDform" name="form_UID" type="hidden">
<input value="" id="form_instance_keyform" name="form_instance_key" type="hidden">
<span class="lia-search-granularity-wrapper">
<select title="Search Granularity" class="lia-search-form-granularity search-granularity" aria-label="Search Granularity" id="searchGranularity" name="searchGranularity">
<option title="All community" value="powerbi|category">All community</option>
<option title="This category" value="PBI_GetHelp|category">This category</option>
<option title="This board" selected="selected" value="power-bi-designer|forum-board">This board</option>
<option title="Knowledge base" value="tkb|tkb">Knowledge base</option>
<option title="Users" value="user|user">Users</option>
</select>
</span>
<span class="lia-search-input-wrapper">
<span class="lia-search-input-field">
<span class="lia-hidden-aria-visibile" id="autocompleteInstructionsText" role="alert"></span><input placeholder="Search all content" aria-label="Search all content" title="Search"
class="lia-form-type-text lia-autocomplete-input search-input lia-search-input-message" value="" id="messageSearchField_0" name="messageSearchField" type="text" aria-autocomplete="both" autocomplete="off">
<div class="lia-autocomplete-container" style="display: none; position: absolute;">
<div class="lia-autocomplete-header">Enter a search word</div>
<div class="lia-autocomplete-content">
<ul></ul>
</div>
<div class="lia-autocomplete-footer">
<a class="lia-link-navigation lia-autocomplete-toggle-off lia-link-ticket-post-action lia-component-search-action-disable-auto-complete" data-lia-action-token="tqZ-cgd1_N419Ee9GCbFJN6m6pHOO4eaFIK6HPqBf68." rel="nofollow" id="disableAutoComplete_128f38e61469ae1" href="https://community.fabric.microsoft.com/t5/forums/v5/forumtopicpage.disableautocomplete:disableautocomplete?t:ac=board-id/power-bi-designer/thread-id/825757&t:cp=action/contributions/searchactions">Turn off suggestions</a>
</div>
</div>
<span class="lia-hidden-aria-visibile" id="autocompleteInstructionsText_0" role="alert"></span><input placeholder="Search all content" aria-label="Search" title="Search"
class="lia-form-type-text lia-autocomplete-input search-input lia-search-input-tkb-article lia-js-hidden" value="" id="messageSearchField_1" name="messageSearchField_0" type="text" aria-autocomplete="both" autocomplete="off">
<div class="lia-autocomplete-container" style="display: none; position: absolute;">
<div class="lia-autocomplete-header">Enter a search word</div>
<div class="lia-autocomplete-content">
<ul></ul>
</div>
<div class="lia-autocomplete-footer">
<a class="lia-link-navigation lia-autocomplete-toggle-off lia-link-ticket-post-action lia-component-search-action-disable-auto-complete" data-lia-action-token="HimINS1BNYo57SEobh_x7N3Y2BLgoSjESb8XLCj8joA." rel="nofollow" id="disableAutoComplete_128f38e61be6981" href="https://community.fabric.microsoft.com/t5/forums/v5/forumtopicpage.disableautocomplete:disableautocomplete?t:ac=board-id/power-bi-designer/thread-id/825757&t:cp=action/contributions/searchactions">Turn off suggestions</a>
</div>
</div>
<span class="lia-hidden-aria-visibile" id="autocompleteInstructionsText_1" role="alert"></span><input placeholder="Enter a keyword to search within the users" ng-non-bindable="" title="Enter a user name or rank"
class="lia-form-type-text UserSearchField lia-search-input-user search-input lia-js-hidden lia-autocomplete-input" aria-label="Enter a user name or rank" value="" id="userSearchField" name="userSearchField" type="text"
aria-autocomplete="both" autocomplete="off"><span class="lia-button-wrapper lia-button-wrapper-secondary lia-button-wrapper-searchForm-action"><input value="searchForm" name="submitContextX" type="hidden"><input
class="lia-button lia-button-secondary lia-button-searchForm-action" value="Search" id="submitContext" name="submitContext" type="submit" title="search"></span>
<div class="lia-autocomplete-container" style="display: none; position: absolute;">
<div class="lia-autocomplete-header">Enter a user name or rank</div>
<div class="lia-autocomplete-content">
<ul></ul>
</div>
<div class="lia-autocomplete-footer">
<a class="lia-link-navigation lia-autocomplete-toggle-off lia-link-ticket-post-action lia-component-search-action-disable-auto-complete" data-lia-action-token="yZq4w-spHCB0u8-6rZgO8Sze8tSwo63vZKl7bWw3roM." rel="nofollow" id="disableAutoComplete_128f38e61f2a934" href="https://community.fabric.microsoft.com/t5/forums/v5/forumtopicpage.disableautocomplete:disableautocomplete?t:ac=board-id/power-bi-designer/thread-id/825757&t:cp=action/contributions/searchactions">Turn off suggestions</a>
</div>
</div>
<span class="lia-hidden-aria-visibile" id="autocompleteInstructionsText_2" role="alert"></span><input placeholder="Enter a keyword to search within the private messages" title="Enter a search word"
class="lia-form-type-text NoteSearchField lia-search-input-note search-input lia-js-hidden lia-autocomplete-input" aria-label="Enter a search word" value="" id="noteSearchField_0" name="noteSearchField" type="text" aria-autocomplete="both"
autocomplete="off">
<div class="lia-autocomplete-container" style="display: none; position: absolute;">
<div class="lia-autocomplete-header">Enter a search word</div>
<div class="lia-autocomplete-content">
<ul></ul>
</div>
<div class="lia-autocomplete-footer">
<a class="lia-link-navigation lia-autocomplete-toggle-off lia-link-ticket-post-action lia-component-search-action-disable-auto-complete" data-lia-action-token="KWmPKxy0QuOUg8heGRb03LHr06GmAEyDJzHRiiIw9i4." rel="nofollow" id="disableAutoComplete_128f38e6227eea0" href="https://community.fabric.microsoft.com/t5/forums/v5/forumtopicpage.disableautocomplete:disableautocomplete?t:ac=board-id/power-bi-designer/thread-id/825757&t:cp=action/contributions/searchactions">Turn off suggestions</a>
</div>
</div>
<span class="lia-hidden-aria-visibile" id="autocompleteInstructionsText_3" role="alert"></span><input title="Enter a search word"
class="lia-form-type-text ProductSearchField lia-search-input-product search-input lia-js-hidden lia-autocomplete-input" aria-label="Enter a search word" value="" id="productSearchField" name="productSearchField" type="text"
aria-autocomplete="both" autocomplete="off">
<div class="lia-autocomplete-container" style="display: none; position: absolute;">
<div class="lia-autocomplete-header">Enter a search word</div>
<div class="lia-autocomplete-content">
<ul></ul>
</div>
<div class="lia-autocomplete-footer">
<a class="lia-link-navigation lia-autocomplete-toggle-off lia-link-ticket-post-action lia-component-search-action-disable-auto-complete" data-lia-action-token="SLHCGOKuXFuDPtjGwMfWYEdbVS-zxZZpSxE-6EmqTDU." rel="nofollow" id="disableAutoComplete_128f38e625ff204" href="https://community.fabric.microsoft.com/t5/forums/v5/forumtopicpage.disableautocomplete:disableautocomplete?t:ac=board-id/power-bi-designer/thread-id/825757&t:cp=action/contributions/searchactions">Turn off suggestions</a>
</div>
</div>
<input class="lia-as-search-action-id" name="as-search-action-id" type="hidden">
</span>
</span>
<span class="lia-cancel-search">cancel</span>
</form>Text Content
skip to main content
JUMPSTART YOUR CAREER WITH THE FABRIC CAREER HUB
Find everything you need to get certified on Fabric—skills challenges, live
sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get started
Skip to main content
Fabric Community
* Forums
* Power BI
* Synapse
* Data Factory
* Real-Time Intelligence
* Galleries
* Power BI
* Synapse
* Data Factory
* Real-Time Intelligence
* Fabric PowerPoint Presentations
* Community News
* Ideas
* User Groups
* Updates Blog
* Learning
* Career Hub
* Cloud Skills Challenge
* Career & Learning Discussion
* Learn Modules
* Tutorials
* Documentation
* Resources
* Community blog
* Events
* Support
* Azure Data Community
* More
*
* Register
* ·
* Sign in
* ·
* Help
* ·
Go To
* Power BI forums
* Updates
* News & Announcements
* Get Help with Power BI
* Desktop
* Service
* Report Server
* Power Query
* Mobile Apps
* Developer
* DAX Commands and Tips
* Custom Visuals Development Discussion
* Health and Life Sciences
* Power BI Spanish forums
* Translated Spanish Desktop
* Power Platform Integration - Better Together!
* Power Platform Integrations (Read-only)
* Power Platform and Dynamics 365 Integrations (Read-only)
* Training and Consulting
* Instructor Led Training
* Dashboard in a Day for Women, by Women
* Galleries
* Community Connections & How-To Videos
* COVID-19 Data Stories Gallery
* Themes Gallery
* Data Stories Gallery
* R Script Showcase
* Webinars and Video Gallery
* Quick Measures Gallery
* 2021 MSBizAppsSummit Gallery
* 2020 MSBizAppsSummit Gallery
* 2019 MSBizAppsSummit Gallery
* Events
* Ideas
* Custom Visuals Ideas
* Issues
* Issues
* Events
* Upcoming Events
* Community Blog
* Power BI Community Blog
* Custom Visuals Community Blog
* Community Support
* Community Accounts & Registration
* Using the Community
* Community Feedback
All communityThis categoryThis boardKnowledge baseUsers
Enter a search word
Turn off suggestions
Enter a search word
Turn off suggestions
Enter a user name or rank
Turn off suggestions
Enter a search word
Turn off suggestions
Enter a search word
Turn off suggestions
cancel
Turn on suggestions
Showing results for
Search instead for
Did you mean:
Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30
Days to Learn It challenge.
* Power BI forums
*
* Forums
*
* Get Help with Power BI
*
* Desktop
*
* Create column with count of entries by ID
CREATE COLUMN WITH COUNT OF ENTRIES BY ID
Reply
Topic Options
* Subscribe to RSS Feed
*
* Mark Topic as New
* Mark Topic as Read
*
* Float this Topic for Current User
* Bookmark
* Subscribe
*
* Printer Friendly Page
* All forum topics
* Previous Topic
* Next Topic
Anonymous
Not applicable
* Mark as New
* Bookmark
* Subscribe
* Mute
*
* Subscribe to RSS Feed
*
* Permalink
* Print
*
* Report Inappropriate Content
CREATE COLUMN WITH COUNT OF ENTRIES BY ID
01-10-2022 05:21 AM
Hi! I am relatively new to DAX, and I've been trying to replicate something like
the group_by function in R.
I wrote some sample data below, with the column "count" being what I need. In
sum, I need to create a column (and not a table) that displays how many entries
an identifier has in the sample EmployeeID column, filtered by hours > 0.
Essentially I need to count the entries by ID.
I tried solutions with GROUPBY that I've seen here but they output a table, and
not a column.
Employee ID HoursCount000102000172000172000241000372000372000402000422000432
Thanks!
Solved! Go to Solution.
Labels:
* Labels:
* Need Help
Message 1 of 7
2,559 Views
0
Reply
1 ACCEPTED SOLUTION
amitchandak
Super User
* Mark as New
* Bookmark
* Subscribe
* Mute
*
* Subscribe to RSS Feed
*
* Permalink
* Print
*
* Report Inappropriate Content
01-10-2022 05:30 AM
@Anonymous , a new column
countx(filter(Table, [Employee ID] =earlier([Employee ID]) && [Hours] >0 ) ,
[Employee ID])
!! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+
Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Proud to be a Super User! !!
View solution in original post
Message 2 of 7
2,539 Views
3
Reply
* All forum topics
* Previous Topic
* Next Topic
6 REPLIES 6
Anonymous
Not applicable
* Mark as New
* Bookmark
* Subscribe
* Mute
*
* Subscribe to RSS Feed
*
* Permalink
* Print
*
* Report Inappropriate Content
01-11-2022 04:10 AM
Thanks @amitchandak ! problem solved
Message 7 of 7
2,395 Views
0
Reply
smpa01
Super User
* Mark as New
* Bookmark
* Subscribe
* Mute
*
* Subscribe to RSS Feed
*
* Permalink
* Print
*
* Report Inappropriate Content
01-10-2022 06:01 AM
@Anonymous you need a measure like this
Measure =
CALCULATE (
COUNT ( 'Table'[Hours] ),
ALLEXCEPT ( 'Table', 'Table'[Employee ID ] ),
'Table'[Hours] <> BLANK ()
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Others:Easing Graph, Animated Calendar
Message 6 of 7
2,432 Views
1
Reply
Anonymous
Not applicable
* Mark as New
* Bookmark
* Subscribe
* Mute
*
* Subscribe to RSS Feed
*
* Permalink
* Print
*
* Report Inappropriate Content
01-10-2022 05:42 AM
Hi @amitchandak , thank you for the quick reply!
I tried that solution, but the output is inaccurate: the count doesn't
correspond to the actual values. For instance, using the sample provided, I can
see that even for IDs that only have 0 hours (which we filtered), the count is
f.ex. 16, and even as the number of entries is f.ex. 4. Plus there are also some
blanks. Do you know what could be wrong?
Thanks again!
EmployeeID HoursCount00010160001016
0001
0
16
0002
5
10
0002
3
10
Message 3 of 7
2,494 Views
0
Reply
amitchandak
Super User
In response to Anonymous
* Mark as New
* Bookmark
* Subscribe
* Mute
*
* Subscribe to RSS Feed
*
* Permalink
* Print
*
* Report Inappropriate Content
01-10-2022 05:48 AM
@Anonymous , I tried same as new column, and did not 16 and 10
Column = COUNTX(FILTER(Data, [EmployeeID] =EARLIER(Data[EmployeeID]) && [ Hours] >0 ), Data[EmployeeID])
!! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+
Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Proud to be a Super User! !!
Message 4 of 7
2,468 Views
0
Reply
Anonymous
Not applicable
In response to amitchandak
* Mark as New
* Bookmark
* Subscribe
* Mute
*
* Subscribe to RSS Feed
*
* Permalink
* Print
*
* Report Inappropriate Content
01-10-2022 05:51 AM
Sorry, I didn't express myself correctly. I meant that I tried the solution in
my original dataset (which is proprietary), and the output was something like
that second table I inserted.
Message 5 of 7
2,452 Views
0
Reply
amitchandak
Super User
* Mark as New
* Bookmark
* Subscribe
* Mute
*
* Subscribe to RSS Feed
*
* Permalink
* Print
*
* Report Inappropriate Content
01-10-2022 05:30 AM
@Anonymous , a new column
countx(filter(Table, [Employee ID] =earlier([Employee ID]) && [Hours] >0 ) ,
[Employee ID])
!! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+
Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Proud to be a Super User! !!
Message 2 of 7
2,540 Views
3
Reply
Post Reply
HELPFUL RESOURCES
Announcements
NEW FORUM BOARDS AVAILABLE IN REAL-TIME INTELLIGENCE.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Get Help
FABRIC MONTHLY UPDATE - MAY 2024
Check out the May 2024 Fabric update to learn about new features.
Learn More
FABRIC CERTIFICATIONS SURVEY
Certification feedback opportunity for the community.
Take Survey
123
PlayStop
PlayStop
PreviousNext
View All
Recommendations
Subject Author Posted
CREATE DUPLICATE DEFECT ENTRIES BASED ON COLUMN VA...
carlin93
02-19-2024 11:22 AM
CREATE A CUSTOM COLUMN
san_21
02-13-2024 04:46 AM
COUNTING NULL DATE ENTRIES
TonyPearce1963
07-13-2023 11:34 PM
COUNT OF COLUMNS
newbie9
02-04-2024 10:00 PM
CREATE A CALCULATED COLUMN BY CREATING A LOOP.
Jagen007
03-19-2024 12:18 AM
Featured Topics
HOW TO GET YOUR QUESTION ANSWERED QUICKLY
Greg_Deckler
Top Solution Authors (Last Month)
User Count
Ritaf1983
95
lbendlin
86
amitchandak
78
Ashish_Mathur
72
ryan_mayu
67
View All
Top Kudoed Authors (Last Month)
User Count
lbendlin
110
amitchandak
104
Ashish_Mathur
84
Greg_Deckler
65
Ritaf1983
63
View All
Users online (4,834)
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
* © 2024 Microsoft
* Manage Cookies
* Privacy & cookies
* Terms of use
* Trademarks
Your Privacy Choices Opt-Out Icon Your Privacy Choices Consumer Health Privacy
Auto-suggest helps you quickly narrow down your search results by suggesting
possible matches as you type.