cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## 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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors