cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbruns
New Member

Need to Count Distinct Dates by Id Number

Hi All,

I have the below example of my data. I need to count the distinct dates (excluding blanks) by Id. I'm ultimately trying to find out how many ids have multiple dates. The Is Current column indicates the latest row of data. So.. in a month from now for Id = 181209 it's possible for it to have another date and therefore another line in the data. 

DateIdIs Current
1/15/2022181209FALSE
2/12/2022181209TRUE
 200077FALSE
 200077TRUE
2/12/20221048707FALSE
2/12/20221048707TRUE

 

This is what I'm trying to get:

DateWork Item IdIs CurrentCountMultiple_Dates
1/15/2022181209FALSE2Yes
2/12/2022181209TRUE2Yes
 200077FALSE  
 200077TRUE  
2/12/20221048707FALSE1No
2/12/20221048707TRUE1No

In the new version of the table, I have a count of date changes by Id. The first one, 181209 has had two dates associated with it, the second doesn't have any dates at all, and the last one continues to have the same date. I want to use the multiple dates column as an indicator for something else.  

I can't seem to get it to calculate by group correctly. This is what I've tried so far:

Count = CALCULATE(DISTINCTCOUNTNOBLANK('Table'[Date]),GROUPBY('Table','Table'[Id]))

This gives me the following:

DateWork Item IdIs CurrentCount
1/15/2022181209FALSE1
2/12/2022181209TRUE1
 200077FALSE 
 200077TRUE 
2/12/20221048707FALSE1
2/12/20221048707TRUE1

 

Can anyone help with this and let me know what I'm missing?

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Try ALLEXCEPT instead of GROUPBY.

Count =
CALCULATE (
    DISTINCTCOUNTNOBLANK ( 'Table'[Date] ),
    ALLEXCEPT ( 'Table', 'Table'[Id] )
)

View solution in original post

3 REPLIES 3
jbruns
New Member

This worked. Thank you!

v-stephen-msft
Community Support
Community Support

Hi  @jbruns ,

 

@AlexisOlson 's formula worked.

Then select 'Show items with no data', which will show rows with no data.

vstephenmsft_0-1643338341208.png

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

AlexisOlson
Super User
Super User

Try ALLEXCEPT instead of GROUPBY.

Count =
CALCULATE (
    DISTINCTCOUNTNOBLANK ( 'Table'[Date] ),
    ALLEXCEPT ( 'Table', 'Table'[Id] )
)

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!