DAX for counting overlapping date ranges across multiple fact tables

[db_halifax]

Dear PBI Super Users,

I have two fact tables each containing events with a start date and end date.  Following the guidance provided in this article: Many-to-many relationship guidance - Power BI | Microsoft Docs ,  rather than relate the fact tables directly with a many to many relationship, I arranged them a star schema with the associated dimension tables in Power BI as shown here:

The data in each fact table is as follows.

FACT_EVENTS_A

LOC_ID	EVENT_TYPE	START_DT	END_DT
1	C	1/2/2022	1/2/2022
1	B	1/8/2022	1/10/2022
2	B	1/4/2022	1/5/2022
2	B	1/6/2022	1/7/2022
3	A	1/1/2022	1/1/2022

FACT_EVENTS_B

LOC_ID	EVENT_TYPE	START_DT	END_DT
1	A	1/1/2022	1/2/2022
1	B	1/4/2022	1/7/2022
2	B	1/3/2022	1/6/2022
2	A	1/7/2022	1/9/2022
3	C	1/10/2022	1/10/2022

I need to count, for each location, the number of overlapping events in FACT_EVENT_B with respect to the location in FACT_EVENT_A.  If I were to do this in T-SQL I would use a left join in something like the following query:

---------------------------------

SELECT A.LOC_ID

   ,COUNT(CASE WHEN B.START_DT <= A.END_DT AND B.END_DT >= A.START_DT THEN A.LOC ELSE NULL END) AS OVERLAP_EVENTS_CNT

FROM FACT_EVENTS_A AS A LEFT OUTER JOIN FACT_EVENTS_B AS B ON A.LOC_ID=B.LOC_ID

GROUP BY A.LOC_ID

---------------------------------

... which yields the following result:

LOC_ID	OVERLAP_EVENTS_CNT
1	1
2	3
3	0

My question is, how do I translate the above query into DAX?

Thank you,

[db_halifax]

Clarification on request:  I would like to set up a calculated column or measure which can be used to produce the same results as shown in the second column of the example results shown above (OVERLAP_EVENTS_CNT).

[BA_Pete]

Hi @db_halifax ,

Do you have to do this in DAX, or is Power Query available to you?

For this kind of thing, you can virtually translate the SQL to Power Query M code step by step.

For reference, here's how I'd do it in Power Query:

//First table, call this factEventsA
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIGYkN9I30jAyMjZGasDkTeCSxogZA3NEAoMIIrMEEoMMUmb4aQN0fIGwMFHCGmIlkAlY8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LOC_ID = _t, EVENT_TYPE = _t, START_DT = _t, END_DT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOC_ID", type text}, {"EVENT_TYPE", type text}, {"START_DT", type date}, {"END_DT", type date}})
in
    #"Changed Type"

//Second table, call this factEventsB
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkN9Q30jAyMjMNMIwozVgcg7gQVNEPLmCHkjuLwxQt4MVd4RWROIaYmQNwYKOEPsN0AogLFjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LOC_ID = _t, EVENT_TYPE = _t, START_DT = _t, END_DT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOC_ID", type text}, {"EVENT_TYPE", type text}, {"START_DT", type date}, {"END_DT", type date}})
in
    #"Changed Type"

//Merged verlap events table
let
    Source = Table.NestedJoin(factEventsA, {"LOC_ID"}, factEventsB, {"LOC_ID"}, "factEventsB", JoinKind.LeftOuter),
    expandMergeAB = Table.ExpandTableColumn(Source, "factEventsB", {"START_DT", "END_DT"}, {"START_DT_B", "END_DT_B"}),
    addOverlapFlag = Table.AddColumn(expandMergeAB, "overlapFlag", each if [START_DT_B] <= [END_DT] and [END_DT_B] >= [START_DT] then 1 else null),
    groupLocID = Table.Group(addOverlapFlag, {"LOC_ID"}, {{"noofOverlapEvent", each List.Sum([overlapFlag]), type nullable number}})
in
    groupLocID

This gives me the following output, that can be applied to the model and used easily in other measures:

Pete

[db_halifax]

Thanks, Pete.   However, I am not familiar with using Power Query, so while the code is appreciated, I do not know how to incorporate it into my report.  I am at least somewhat familiar with how to set up a measure or calculated column in Power BI using DAX.

[BA_Pete · Accepted solution]

@db_halifax ,

No problem, I understand.

Here's a walkthrough of how to do this in Power Query. If you still specifically want DAX after trying this then let me know and I'll pass over to a DAX-hero superuser as I can't figure out how to do what you want with your current model structure within a reasonable amount of time (mainly due to the behaviour of NATURALLEFTOUTERJOIN in DAX i.e. not being able to complete this join on only one field).

1) In Power BI Desktop, on the Home tab, hit Transform Data on the ribbon.

2) In the Power Query Editor that opens, select your EventsA table, then on the Home tab select the little dropdown next to Merge and select Merge Queries As New.

3) In the dialog, select the LOC_ID field in the top preview. Select the EventsB table from the dropdown just below and select the LOC_ID field on the preview. It should default to Left Outer join type so hit OK.

4) Your resulting table should look something like this:

Hit the button circled in the picture to expand your EventsB [START_DT] and [END_DT] columns.

5) On the Add Column tab select Custom Column and enter this in the calculation window replacing '[START_DT_B]' and '[END_DT_B]' with whatever your expanded columns are called:

if [START_DT_B] <= [END_DT] and [END_DT_B] >= [START_DT] then 1 else null

6) Select the [LOC_ID] column and on the Home tab select Group By. In the dialog, under New Column Name, name your overlap events column, under operation selct SUM, and from the column dropdown select the column that you created in step 5.

You can then apply his to the model and relate to your DIM_LOCATION table to have the info available when/wherever you need it.

Pete

[db_halifax]

Thanks, Pete!

Your solution worked.   There is clearly a missing section in the "many-to-many relationship guidance" document.   I have also learned that Power Query needs to be in my toolbox.

[BA_Pete]

@db_halifax 

Great news! Happy to hear it's worked out for you.

Also, yes, Power Query absolutely HAS to be in your toolbox. There's always a trade-off between whether you do some things in M or DAX, but there's some things that can ONLY be done in one or the other.

Additionally, the more you do in Power Query before sending to the data model, the less the enduser system has to use memory to get things done, speeding up response times and improving enduser experience.

Have a good weekend 🙂

Pete

[db_halifax]

Pete,

I need to generate the result and show it in a Power BI report.  Can I do that using Power Query or must I use DAX?

Thanks,