Get the first date and time from within a group

Question

Many of the questions we get asked are related to how much time passes between two events (e.g. - patient was admitted, some assessment occurred). Some events take place multiple times. How can I find the first date and time that an event occurred per patient visit?

In the model is a table called Clinical Queries. In that table is a column called Activity Date Time. There is a calculated column called Date and another called Time that are based off of the Activity Date Time. These are related to a Date dimension table and a Time dimension table.

I've managed to build a query that gives me the earliest date, and it gives me the earliest time. However, it returns the earliest time regardless of the date. For example, if a patient has two assessments performed, one on 1/1/2017 at 23:59 and another on 1/2/2017 at 00:01, the query returns 1/1/2017 at 00:01.

WITH
MEMBER [Measures].[FirstInterventionDate] AS (
    NonEmpty(existing{[Date].[Fiscal].[Date].MEMBERS}, {[Measures].[Clinical Queries Interventions Performed]}).Item(0).Name
)

MEMBER [Measures].[FirstInterventionTime] AS (
    NonEmpty(existing{[Time].[Time].[Time].MEMBERS}, {[Measures].[Clinical Queries Interventions Performed]}).Item(0).Name
)

SELECT {[Measures].[Clinical Queries Interventions Performed], [Measures].[FirstInterventionDate], [Measures].[FirstInterventionTime]} on 0,
    NON EMPTY([Clinical Queries].[Account Number].Children) ON 1
FROM (
SELECT { [Clinical Queries].[InterventionID].&[3000195],
    [Clinical Queries].[InterventionID].&[3000186],
    [Clinical Queries].[InterventionID].&[3000184],
    [Clinical Queries].[InterventionID].&[3000182],
    [Clinical Queries].[InterventionID].&[3000184] } ON 0
FROM (
SELECT { [Clinical Queries].[Account Number].&[ACCT992],
    [Clinical Queries].[Account Number].&[ACCT064] } ON 0
FROM [Model]
))

What do I need to change to make it return the earliest date and time for that earliest date. For the example above it would be 1/1/2017 at 23:59.

Answer 1

Try the following code:

select 
    {[Clinical Queries].[Account Number].&[ACCT992],
     [Clinical Queries].[Account Number].&[ACCT064]} *
    {[Measures].[Clinical Queries Interventions Performed]} on 0,
    Generate(
        {[Clinical Queries].[InterventionID].&[3000195],
         [Clinical Queries].[InterventionID].&[3000186],
         [Clinical Queries].[InterventionID].&[3000184],
         [Clinical Queries].[InterventionID].&[3000182],
         [Clinical Queries].[InterventionID].&[3000184]},
        Head(
            NonEmpty(
                [Clinical Queries].[InterventionID].CurrentMember *
                [Date].[Fiscal].[Date].Members *
                [Time].[Time].[Time].Members,
                [Measures].[Clinical Queries Interventions Performed]
            ),
            1
        )
    ) on 1
from [Model]

You may be not very happy about performance, so I strongly recommend you to move the logic on DWH level: add a measure field like 201703301020 (3/30/2017 10:20) with Min aggregation, you may parse the value using MDX calculated measure combined with left() right() functions.