topic Re: Is there a way to count the amount of date overlaps? in DAX Commands and Tips

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

Hell-1931 — Fri, 21 Feb 2020 08:11:41 GMT

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!

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

Anonymous — Fri, 21 Feb 2020 11:15:39 GMT

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... 😉

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

Anonymous — Sat, 22 Feb 2020 14:39:49 GMT

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.

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

Anonymous — Sat, 22 Feb 2020 15:08:05 GMT

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

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

Anonymous — Sat, 22 Feb 2020 15:02:34 GMT

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

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

Anonymous — Sat, 22 Feb 2020 20:39:35 GMT

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.

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

Anonymous — Sat, 22 Feb 2020 21:08:08 GMT

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

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

Hell-1931 — Sat, 22 Feb 2020 23:35:13 GMT

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

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

Hell-1931 — Sat, 22 Feb 2020 23:41:51 GMT

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)...

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

Anonymous — Sun, 23 Feb 2020 12:35:16 GMT

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

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

Hell-1931 — Mon, 24 Feb 2020 05:31:57 GMT

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

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

Anonymous — Mon, 24 Feb 2020 14:44:31 GMT

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

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

Anonymous — Mon, 24 Feb 2020 14:42:49 GMT

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

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

Hell-1931 — Tue, 25 Feb 2020 06:35:25 GMT

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)

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

Anonymous — Tue, 25 Feb 2020 08:45:08 GMT

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

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

Anonymous — Tue, 25 Feb 2020 08:46:52 GMT

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

Best
D

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

Anonymous — Tue, 23 Jun 2020 08:38:42 GMT

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