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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
davidi4524
Helper III
Helper III

counting records per user

hi, i have a table that contain the fo,owing colums:

 

name           date

jonthan       19/11/2017

jonthan       20/11/2017

josef             20/11/2017

omar             20/11/2017

jonthan          20/11/2017

josef               19/11/2017

 

i whant to create a related table that show the folowing information when i select the dates between 19/11/2017 and 20/11/2017

 

name      total days

jonthan       3

josef            2 

omar            1 

 

what is the correct syntax to create  a calculate column ??

 

thanks.

2 ACCEPTED SOLUTIONS

Hi @davidi4524,

 

Look at your original question carefully - COUNT is exactly what you wanted.  Anyways, for your changed question now, try this

 

=DISTINCTCOUNT(Data[Date])


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

View solution in original post

Anonymous
Not applicable

If you want a distinct count for my solution, simply select 'Distinct Count' from the dropdown instead of 'Count'.

 

 

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

If you want simply display the table, you can do that with just a couple clicks using the Table visual. Just drag the 'Name' column onto the table twice. Click the drop down arrow and select 'Count'. Then, create a filter using your Date column. Be sure that it's formatted as a date or date/time or you won't see the slider, you'll see check boxes.

 

 

If you want to create a brand new table...

  1. Enter the Query Editor
  2. Right-click the table and select 'Duplicate'
  3. With the new table selected, click 'Group By' from the 'Transform' section of the home ribbon
  4. Choose to group by your Name column
  5. Choose the Operation 'Count Rows'
  6. Click 'OK'

ok but i need to count distinct the numbers of the records per the username between dates. this solusion is only make a subtotal per username

Anonymous
Not applicable

If you want a distinct count for my solution, simply select 'Distinct Count' from the dropdown instead of 'Count'.

 

 

ok but i need to count distinct the numbers of the records per the username between dates. this solusion is only make a subtotal per username

Hi @davidi4524,

 

Look at your original question carefully - COUNT is exactly what you wanted.  Anyways, for your changed question now, try this

 

=DISTINCTCOUNT(Data[Date])


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

Hi,

 

This should not be a calculated column but a measure.  Drag the Name, create a date slicer  and make any selection.  Then use this measure

 

=COUNTROWS(Data)

 

Hope this helps.


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

sorry, i'm new to the dax language.  what execly the syntax to make it?

Hi @davidi4524,

 

Have you tried the solution provided above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

 

If you still have any question on this issue, feel free to post here. Smiley Happy

 

Regards

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.