cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

## Distinct Count of ID's associated with a given date from rows with start and end dates.

Hello!

I am trying to get a distinct count of ID's noted within a specific date range.  My table has columns with ID's, start and end dates.

I can get the total rows that fall within the date range on any given day from the following:

AL Apt Census =
VAR StartDate = VALUE(SELECTEDVALUE( 'Assisted Living MXC Census'[MOVEINDATE] ) )
VAR EndDate = VALUE(SELECTEDVALUE( 'Assisted Living MXC Census'[ENDDATE] ) )
VAR MinDate = VALUE( MIN( Dates[DayDate] ) )
VAR MaxDate = VALUE( MAX( Dates[DayDate] ) )
VAR CensusPrimary = SWITCH(TRUE(),
SELECTEDVALUE('Assisted Living MXC Census'[PAYORTYPENAME]) = "Primary" || SELECTEDVALUE('Assisted Living MXC Census'[PAYORTYPENAME]) = "Couple",
IF( AND( StartDate <= MinDate, EndDate >= MinDate ) ,
MIN(EndDate, MaxDate ) - MinDate +1,
IF( AND( AND( StartDate >= MinDate, StartDate <= MaxDate), EndDate >= MinDate),
MIN( EndDate, MaxDate) - StartDate +1,
BLANK()) ),BLANK())
RETURN
CensusPrimary

Is there anyway to get a distinct count of a value noted on any given day?  I need the distinct count of the ID's assocated with this census number.  I am stuck.

Thank you for any help!
1 ACCEPTED SOLUTION
Community Support

Hi,

According to your description, I can roughly understand your requirement, you want to get a distinct count of ID's noted within a specific date range, right? I think you can try this method to get the expected result:

This is the test data I created based on your description:

You can create a calendar table to be placed into the slicer:

``Calendar = CALENDAR(DATE(2022,1,1),DATE(2022,1,5))``

Then you can create a measure like this:

``````Distinct count =

var _start=MINX(ALLSELECTED('Calendar'),'Calendar'[Date])

var _end=MAXX(ALLSELECTED('Calendar'),'Calendar'[Date])

return

CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),[Start Date]>=_start&&[End Date]<=_end))``````

And you can create a slicer and a card chart to get what you want, like this:

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

2 REPLIES 2
Community Support

Hi,

According to your description, I can roughly understand your requirement, you want to get a distinct count of ID's noted within a specific date range, right? I think you can try this method to get the expected result:

This is the test data I created based on your description:

You can create a calendar table to be placed into the slicer:

``Calendar = CALENDAR(DATE(2022,1,1),DATE(2022,1,5))``

Then you can create a measure like this:

``````Distinct count =

var _start=MINX(ALLSELECTED('Calendar'),'Calendar'[Date])

var _end=MAXX(ALLSELECTED('Calendar'),'Calendar'[Date])

return

CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),[Start Date]>=_start&&[End Date]<=_end))``````

And you can create a slicer and a card chart to get what you want, like this:

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

Super User

I think you might be making life hard for yourself with the nested SWITCH/If when you could be using CALCULATE.

Are you able to share a demo pbix? Fairly sure we should be able to help.

Ben

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors