Using a calculated column from a filtered table as the legend of a visualisation

[MichaelHanksSF]

Hello, I have a table ("Events") which has events data linked to individuals with IDs. Events begin on a "Commenced Date" and end on a "Ceased Date". For each event, the age of the individual at the start of the event is provided. All of this has been created in Power Query and loaded into Power BI.

I have a separate date table ("Date") with daily increments of days for the range of time covered by the events table. I want report users to be able to set a date range and see measures and results that are relevant for the period set.

For example, I have created several measures that return a single value from such filtering, such as the number of unique individuals with open events during the range:

Open Event Distinct ID Count = 
CALCULATE(
    DISTINCTCOUNT('Events'[ID]),
    FILTER(
        'Events',
        'Events'[Commenced Date] <= MAX('Date'[Date]) &&
        'Events'[Ceased Date] > MIN('Date'[Date])
        )
    ) + 0

These work just fine. However, now I want to create a visualisation that shows unique individuals by the age they were for only their most recent open event during the user-defined period.

If I simply use the measure above along with the age as a legend, I will get multiple counts of the same individual if they had multiple events open during the period where they were at different ages at the start.

I have been able to create a calculated table that provides me with the calculated column that I want, in two stages:

Date filtered events = 
CALCULATETABLE(
    'Events',
    'Events'[Commenced Date] <= MAX('Date'[Date]) &&
    'Events'[Ceased Date] > MIN('Date'[Date])
    )

Distinct ID w/ age in most recent event = 
SUMMARIZE(
    'Date filtered events',
    'Date filtered events'[ID],
    "Age at most recent event", MAX('Date filtered events'[Age])
)

For a fixed, pre-determined date range, summarising over this table provides me with the correct answer. However, it cannot be filtered by a user-defined date range and is therefore no use to me.

What I need is to be able to produce this as a measure somehow, with the distinct count of 'ID' per 'Age' for the given user-defined 'Date' range.

Can anyone help me with this, please?

Here is some sample data.

ID	Commenced Date	Ceased Date	Age
1	01/01/2020	15/03/2020	5
1	15/03/2020	25/07/2022	5
1	25/07/2022	12/12/2022	7
2	01/03/2020	25/11/2021	10
2	25/11/2021	15/10/2022	12
2	15/10/2022	13/05/2023	13

For this sample data, if a user selected a date range of 01/01/2020 - 31/12/2021, currently my visualisation for distinct count of ID with age as legend (using the measure provided above) would have underlying data:

Age	Distinct ID Count
5	1
10	1
12	1

However, I want it to have:

Age	Distinct ID Count
5	1
12	1

Thanks in advance!

[Anonymous · Accepted solution]

Hi @MichaelHanksSF ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a date table first, then put the date column into the slicer.

Table 2 = CALENDAR(DATE(2020,1,1),DATE(2021,12,31))

Then create a measure.

Measure = var _1=MAXX(ALLSELECTED('Table 2'),'Table 2'[Date])
var _2=MINX(ALLSELECTED('Table 2'),'Table 2'[Date])
return
CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[Commenced Date]>=_2&&'Table'[Commenced Date]<=_1&&'Table'[Age]=SELECTEDVALUE('Table'[Age])))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[lbendlin]

I have a separate date table ("Date") with daily increments of days for the range of time covered by the events table.

That sounds ... weird?  Wouldn't you rather have a Range slicer for that? Or better yet, educate your users on how to use the filter pane.