Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
// 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
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...
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... 😉
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)...
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...
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
Hello, is it possible to modify tgis measure, if i need a count of overlaping days?
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
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)
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |