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
Anonymous
Not applicable

Count of Dates

Dear All,

 

I am trying to count user wise distinct date but not able to do. I have datetime columns so my measure is not giving correct counts because of time in the date.

 

Team Working Days = CALCULATE(DISTINCTCOUNT(Activity_Detail[Activity_Insert_At]), ALLSELECTED(Activity_Detail[Activity_Insert_At]))
 
With this measure, counts is not correct. In filter , I have selected May but below counts is coming -
erro.png
1 ACCEPTED SOLUTION

@Anonymous - Really difficult without the source data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

 

But, maybe count all of your rows of users and subtract this count? Just purely guessing at this point due to lack of information.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

You could try:

 

Team Working Days = CALCULATE(DISTINCTCOUNT(Activity_Detail[Activity_Insert_At]), ALLSELECTED(Activity_Detail[Activity_Insert_At]))

 

Team Working Days = 

VAR __Table = 

  ADDCOLUMNS(

    ALLSELECTED(Activity_Detail[Activity_Insert_At]),

    "Date Only",INT(Activity_Detail[Activity_Insert_At])

  )

RETURN

  COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"Date",[Date Only])))

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@amitchandak @Greg_Deckler  thanks for ur help,

 

@Greg_Deckler - after using your measure , now 85 counts showing for all users -

erro.png

@Anonymous - Is Activity_Insert_At your date/time column? Maybe just:

 

Team Working Days = 

VAR __Table = 

  ADDCOLUMNS(

    Activity_Detail[Activity_Insert_At],

    "Date Only",INT(Activity_Detail[Activity_Insert_At])

  )

RETURN

  COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"Date",[Date Only])))

 

 

If not, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Dear @Greg_Deckler ,

Now counts are coming perfectly fine. But it is coming for users who have done activity. 

I need those users details also who have not done any activity with "0" count.?

 

Kindly suggest me on this also. 🙂

Thanks Again for your help.

@Anonymous - Really difficult without the source data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

 

But, maybe count all of your rows of users and subtract this count? Just purely guessing at this point due to lack of information.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi @Greg_Deckler 

Thanks for guideing me to raise a better complaint. Next time i will take care of it.

By the way, After adding "0" i the end , I am getting those users also who have not done any activity in the selected date range -

 

Thanks for helping me out in the same.

 

 

amitchandak
Super User
Super User

@Anonymous , I doubt you Activity_Insert_At has timestamp , Change the format to include timestamp and check

 

Create a date and use

Activity_Insert_Date = [Activity_Insert_At].Date

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.