Is there a way to count the amount of date overlaps?

[Hell-1931]

Hi,

I have the following Table:

Where DischDate = '2020-02-20' means no discharge date; just to replace NULL value

Is there way in DAX Power BI - to calculate the amount of Overlapping dates for each Client (EHRClientFK), as per following

"Overlap" schema:

According to the schema only EHRClientFK values dates = 2,3,4,5 should be counted

Values 8,9,10 have are only single; values 6,7 have no Ovrelaps

So, my goal is to have something like:

EHRClientFK   # of Overlapping Dates

2                     2

3                     2

4                     2

5                     2

Thank you for any suggestions!

[Anonymous]

// Assumption:
// for each EHRClientFK there are
// at most 2 records in the table
// 'Data' and AdmDate and DischDate
// can't be BLANK. Also,
// AdmDate <= DischDate.

[# Overlaps] :=
var __oneClientVisible = hasonevalue(Data[EHRClientFK])
var __result = if(__oneClientVisible,
	
	var __rowsVisible = countrows(Data)
	var __maxOfMins =
		max(Data[AdmDate])
	var __minOfMaxes =
		min(Data[DischDate])
	var __isOverlap =
		// you have to decide what kind of
		// overlap you want, that is whether
		// to use < or <=
		__maxofMins < __minOfMaxes
	return
		switch(__rowsVisible
			1, 1,
			2, 2 * __isOverlap,
			
			// This should not happen
			-1
		)
	
)
return
	__result

Please analyze the code and adjust according to your needs.

Best

D

[Anonymous]

OK. It looks like the condition for overlapping periods is max(min(P1), min(P2)) < min(max(P1), max(P2)), where min([a,b])=a and max([a,b]) = b. Question: Is it true that the maximum number of overlaps for a client is 2? Please answer it and then I'll give you a simple measure.

[Hell-1931]

Answering your question - No, There could be more than 2 periods for one client.

As many periods as there are overlaps according to my schema...

[Anonymous]

To see the number of overlaps per row in Table you could use a measure like this:

# of Overlapping Dates 1 =
SUMX (
    'Table';
    COUNTROWS (
        FILTER (
            ALL ( 'Table' );
            [EHRClientFK] = EARLIER ( [EHRClientFK] )
                && 'Table'[AdmDate] <= EARLIER ( 'Table'[DischDate] )
                && 'Table'[DischDate] >= EARLIER ( 'Table'[AdmDate] )
        )
    )
)

The problem with this is that every single row will count itself as overlapping. So, we need to add a check for that. If there is some kind of unique column in the table, use that one. I assume there are no duplicates and add the check like this:

# of Overlapping Dates 2 =
SUMX (
    'Table';
    COUNTROWS (
        FILTER (
            ALL ( 'Table' );
            [EHRClientFK] = EARLIER ( [EHRClientFK] )
                && 'Table'[AdmDate] <= EARLIER ( 'Table'[DischDate] )
                && 'Table'[DischDate] >= EARLIER ( 'Table'[AdmDate] )
                && NOT (
                    'Table'[DischDate] = EARLIER ( 'Table'[DischDate] )
                        && 'Table'[AdmDate] = EARLIER ( 'Table'[AdmDate] )
                )
        )
    )
)

This will work if you show all columns of the table. For example both rows with EHRClientFK=2 will show 2. But if we remove the dates those two rows will sum up to 4. not what we wanted. This can be solved by grouping on EHRClientFK and the number of overlaps and then sum:

# of Overlapping Dates 3 =
VAR NewTable =
    ADDCOLUMNS (
        'Table';
        "Overlapping"; COUNTROWS (
            FILTER (
                ALL ( 'Table' );
                [EHRClientFK] = EARLIER ( [EHRClientFK] )
                    && 'Table'[AdmDate] <= EARLIER ( 'Table'[DischDate] )
                    && 'Table'[DischDate] >= EARLIER ( 'Table'[AdmDate] )
                    && NOT (
                        'Table'[DischDate] = EARLIER ( 'Table'[DischDate] )
                            && 'Table'[AdmDate] = EARLIER ( 'Table'[AdmDate] )
                    )
            )
        )
    )
RETURN
    SUMX ( GROUPBY ( NewTable; [EHRClientFK]; [Overlapping] ); [Overlapping] )

This got a little bit complicated for a simple task. Could probably be made easier in some way... 😉

[Hell-1931]

Sorry, a bit confusing... I'll look carefully

But - what if there are more than 3 overlaps? 

There could be as many overlaps as possible ...

EHRClientFK is a foreign key - contains the numbers of episodes (AdmDate <-> DischDate) for the Client...

It could be any numbers of episodes (AdmDate <-> DischDate variations)...  

[Anonymous]

Then you have to show us how you want to deal with some more complex situations. Just show what happens when there are 3 different periods in one selection EHRClientFK. Becasue then the periods can have many more different arrangements than the ones you showed for just 2 periods.

Best
D

[Hell-1931]

If there 3 or more different periods in one selection EHRClientFK - same goal - I want to only count the "Overlap" cases

Doesn't matter - how many periods in EHRClientFK - 3,4,5,6 or more - I want to only count those which are Overlaps

Example -

EHRClientFK    AdmDate       DischDate

2                      12/16/2016    1/26/2017

2                           4/4/2017    11/30/2017

2                           5/8/2018      7/4/2018

2                          7/10/2018  12/31/2050

2                           7/11/2018  11/14/2018

2                            8/9/2018   12/31/2050

In this example case 5/8/2018  <->   7/4/2018 shouldn't be calculated because this one has no overlaps!

So here should be 5 overlapping dates for EHRClientFK  (out of 6 total)

Hope this makes more sense...

[Anonymous]

Please try to use this. This time it should work.

# Overlaps = 
var __oneClientVisible = hasonevalue(Data[EHRClientFK])
var __result = if(__oneClientVisible,
	
    // Bear in mind that if a value is
    // of type boolean then you have
    // to convert it into a number for
    // SUMX to work. To convert it it's
    // OK to multiply it by 1 or add 0 to it.
	var __data = Data
	return
	SUMX(
		__data,
		var __a = Data[AdmDate]
		var __b = Data[DischDate]
		return 0 +
        (
            SUMX(
                __data,
                var __x = Data[AdmDate]
                var __y = Data[DischDate]
                var __maxOfMins =
                    max(__a, __x)
                var __minOfMaxes =
                    min(__b, __y)
                return
                    // Need to decide if < or <=.
                    // If it's possible to have
                    // intervals where both ends
                    // are the same, then you
                    // have to use <=.
                    1 * (__maxOfMins < __minOfMaxes)
            ) > 1
        )
    ) + 0
)
return
	__result

[Anonymous]

Hello, is it possible to modify tgis measure, if i need a count of overlaping days?

[Anonymous]

The measure above should do the trick if AdmDate and DischDate can't be BLANK and AdmDate <= DischDate.

By the way, if you remove the condition on just one ClientFK visible... the measure will also make sense. It'll return the number of overlaps in the selection, whatever the selection is, not necessarily one client only.

Best
D

[Hell-1931]

Thank you, let me try over the next 2 days and I'll get back if it worked!

Btw, just to inform - this Episode table has no unique values

EHRClientFK is a foreign key, refered from the other table where it is a unique values of ClientID

Will this still work?

(I'll try of course) 

[Anonymous]

By the way... If this is THE measure, please give Kudos (the button with the thumbs-up on it).

Best
D

[Anonymous]

Well, whether it gives you what you're looking for or not depends on... what you're looking for. To know whether or not a measure does what you think it does you have to test it on all of your cases... There is no other way unless you can mathematically prove by deduction that it will do what you want it to. Right? 🙂

Best
D

[Anonymous]

@Anonymous, please stop using EARLIER. This is a feature of the language that is deprecated and hard to understand. Instead, please use variables. Thanks.

[Anonymous]

@AnonymousYes, variables are probably better. I wrote a lot of DAX in the early days of Power Pivot and back then EARLIER was the only option. But I can't find any information that it has been deprecated. dax.guide has labeled it "Not recommended", but nothing is mentioned in the official Microsoft docs as far as I can see: https://docs.microsoft.com/en-us/dax/earlier-function-dax.

[Anonymous]

I might have used a wrong word but the gist holds nevertheless. EARLIER is one of the worst functions in DAX (actually it's horrible) and since there are variables, there's no need to use it and confuse people anymore.

Thanks.

Best
D