Events in Progress count measure additionally filtered with scd2 dimension atribute value

[DJura]

Hi,

Thanks in advance for your help.

There are three tables:

1. DimDate
2. Acumulating snapshot fact table (events in progress type) with existing measure “CountOfEvents” (without relations to date table)
3. SCD2 DimEventType table that is filtered with same date and also filters FactEvent. This table contains numeric value “AcceptableWaitingDays” important for this topic.

When we use dates from date table (on row in PBIDesktop), SCD2 table should be filtered and that “AcceptableWaitingDays” (valid on given date) need to be compared to existing FactEvent[EventDuration] value on active event for that date.

An example:

DimEventType:

EventTypeSK	EventTypeBK	EventType	Validfrom	ValidTo	AcceptableWaitingDays
1	111	Event1	01.01.2021	05.01.2021	10
2	111	Event1	06.01.2021	31.01.2021	30

FactEvent

FactID	EventTypeSK	EventTypeBK	StartDate	EndDate	EventDuration
11111	1	111	01.01.2021	31.01.2021	30
22222	2	111	10.01.2021	20.01.2021	10

When we put a date on rows:

It should filter FactEvent so we can get active events (CountOfEvents) and also filter DimEventType, take valid AcceptableWaitingDays, use it as as dynamic value for comparison on fact row level.

That second expanded measure (CountOfOverAcceptableEvents) should return number of active events that is over AcceptableWaitingDays on given date.

So. first row should be counted once in period 1.1.-5.1. and zero times in period 6.1.-31.1.

Thank you.

M.

[DJura]

Thanks in advance for your help.

There are three tables:

1. DimDate
2. Acumulating snapshot fact table (events in progress type) with existing measure “CountOfEvents” (without relations to date table)
3. SCD2 DimEventType table that is filtered with same date and also filters FactEvent. This table contains numeric value “AcceptableWaitingDays” important for this topic.

When we use dates from date table (on row in PBIDesktop), SCD2 table should be filtered and that “AcceptableWaitingDays” (valid on given date) need to be compared to existing FactEvent[EventDuration] value on active event for that date.

An example:

DimEventType:

EventTypeSK	EventTypeBK	EventType	Validfrom	ValidTo	AcceptableWaitingDays
1	111	Event1	01.01.2021	05.01.2021	10
2	111	Event1	06.01.2021	31.01.2021	30

FactEvent

FactID	EventTypeSK	EventTypeBK	StartDate	EndDate	EventDuration
11111	1	111	01.01.2021	31.01.2021	30
22222	2	111	10.01.2021	20.01.2021	10

When we put a date on rows:

It should filter FactEvent so we can get active events (CountOfEvents) and also filter DimEventType, take valid AcceptableWaitingDays, use it as as dynamic value for comparison on fact row level.

That second expanded measure (CountOfOverAcceptableEvents) should return number of active events that is over AcceptableWaitingDays on given date.

So. first row should be counted once in period 1.1.-5.1. and zero times in period 6.1.-31.1.

Thank you.

M.

[DJura]

Thank You, I will try to be more clear.

In standard “Events In progress” pattern fact table is usually filtered with date and some other active one-to-many relation with dimension.  Measure “Count of Active Events” is simple.

In my model dimension table is SCD2 type:

DimEventType

EventTypeSK	EventTypeBK	EventType	Validfrom	ValidTo	AcceptableWaitingDays
1	111	Event1	01.01.2021	05.01.2021	10
2	111	Event1	06.01.2021	31.01.2021	30

FactEvent

FactID	EventTypeSK	EventTypeBK	StartDate	EndDate	EventDuration
11111	1	111	01.01.2021	31.01.2021	30

Sample mode:

So, I cannot create relationship on DimEventType[EventTypeSK] -> FactEvent[EventTypeSK] like in models where is only one "version" of Event1 member but I need virtual relationship to virtualtable (Filtered DimEvent).

Requirement is to create a measure which will count active events on given date which have EventDuration over AcceptableWaitingDays value (defined in related dimension).  

DimDate filters FactEvent and also DimEventType (that filters again FactEvent).

Expected results:

Date	ActiveEvents	IsOverAcceptable
01.01.2021	1	1
02.01.2021	1	1
03.01.2021	1	1
04.01.2021	1	1
05.01.2021	1	1
06.01.2021	1	0
07.01.2021	1	0
08.01.2021	1	0

Alternative solution I have on my mind is to remodel fact table in ETL. 

(Kimball: DesignTip#145  Timespan Accumulating Snapshot Fact Tables) where every change in DimEventType breaks Fact record in many non overlapping (StartDate, EndDate) fact rows. In this case SCD2 DimEventType could be connected to fact table using EventTypeSK (surrogate key) and then AcceptableWaitingDays used as a condition.

(RELATED(AcceptableWaitingDays) < EventDuration)

I'd like to do check here on forum if a measure can be written instead, and if not only then change the model in DWH. 

Thank You.

BR

[Anonymous]

@DJura 

It's Wednesday already... and you've not received any answers. I think the description of the problem is not  easily understandable and people have a hard time following. In order to get an answer you'll have to re-phrase the problem. Please be maximally clear when doing that. If you want some help, here's a good place to start:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/td-...