Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have some data containing ID#, Initiated DateTime Stamp, Finished DataTime Stamp. I also have a DateTime Calendar dimension which is related to the first table toward Initiated TimeStamp.
For example:
ID# Initiated Stamp Finished Stamp
125 29/10/2019 08:00 29/10/2019 12:00
129 29/10/2019 09:00 29/10/2019 14:00
135 29/10/2019 10:00 29/10/2019 15:00
138 29/10/2019 12:00 29/10/2019 13:00
Expected result: how many simultaneously ID# there were though time. Step by Step this is how it should count
@29/10/2019
08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00
1 1 1 1 1 0 0 0
0 1 1 1 1 1 1 0
0 0 1 1 1 1 1 1
0 0 0 0 1 1 0 0
In this example, I should get a visual containing
@29/10/2019
08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00
1 2 2 2 3 3 2 1
This is the measure I've tried, but didn't work as I thoght:
ID# Simultaneously = CALCULATE(COUNTROWS(FactTable);FILTER(FactTable;FactTable[Initiated Stamp]>=RELATED('dCalendar'[DateTime]) || FactTable[Finished Stamp] <= RELATED('dCalendar'[Date])))
Solved! Go to Solution.
hi @Gonzalis
For your case, you could try this formula as below:
Step1:
Create a date table with 24 datetimes per day, you could try this formula:
dCalendar =
ADDCOLUMNS (
CROSSJOIN (
CALENDAR ( MIN(FactTable[Initiated Stamp]), MAX(FactTable[Finished Stamp])),
UNION (
ROW ( "Time", TIME ( 1, 0, 0 ) ),
ROW ( "Time", TIME ( 2, 0, 0 ) ),
ROW ( "Time", TIME ( 3, 0, 0 ) ),
ROW ( "Time", TIME ( 4, 0, 0 ) ),
ROW ( "Time", TIME ( 5, 0, 0 ) ),
ROW ( "Time", TIME ( 6, 0, 0 ) ),
ROW ( "Time", TIME ( 7, 0, 0 ) ),
ROW ( "Time", TIME ( 8, 0, 0 ) ),
ROW ( "Time", TIME ( 9, 0, 0 ) ),
ROW ( "Time", TIME ( 10, 0, 0 ) ),
ROW ( "Time", TIME ( 11, 0, 0 ) ),
ROW ( "Time", TIME ( 12, 0, 0 ) ),
ROW ( "Time", TIME ( 13, 0, 0 ) ),
ROW ( "Time", TIME ( 14, 0, 0 ) ),
ROW ( "Time", TIME ( 15, 0, 0 ) ),
ROW ( "Time", TIME ( 16, 0, 0 ) ),
ROW ( "Time", TIME ( 17, 0, 0 ) ),
ROW ( "Time", TIME ( 18, 0, 0 ) ),
ROW ( "Time", TIME ( 19, 0, 0 ) ),
ROW ( "Time", TIME ( 20, 0, 0 ) ),
ROW ( "Time", TIME ( 21, 0, 0 ) ),
ROW ( "Time", TIME ( 22, 0, 0 ) ),
ROW ( "Time", TIME ( 23, 0, 0 ) ),
ROW ( "Time", TIME ( 24, 0, 0 ) )
)
),
"DateTime", [Date] + [Time],
"Hour", HOUR ( [Time] )
)
Step2:
Don't create the relationship between fact table and this Calendar table.
Step3:
Create a measure as below:
Measure =
CALCULATE (
COUNT ( FactTable[ID# ]),
FILTER (
FactTable,
FactTable[Initiated Stamp] <= SELECTEDVALUE(dCalendar[DateTime])
&& FactTable[Finished Stamp] >= SELECTEDVALUE( dCalendar[DateTime])
))
Result:
and here is sample pbix file, please try it.
Regards,
Lin
hi @Gonzalis
For your case, you could try this formula as below:
Step1:
Create a date table with 24 datetimes per day, you could try this formula:
dCalendar =
ADDCOLUMNS (
CROSSJOIN (
CALENDAR ( MIN(FactTable[Initiated Stamp]), MAX(FactTable[Finished Stamp])),
UNION (
ROW ( "Time", TIME ( 1, 0, 0 ) ),
ROW ( "Time", TIME ( 2, 0, 0 ) ),
ROW ( "Time", TIME ( 3, 0, 0 ) ),
ROW ( "Time", TIME ( 4, 0, 0 ) ),
ROW ( "Time", TIME ( 5, 0, 0 ) ),
ROW ( "Time", TIME ( 6, 0, 0 ) ),
ROW ( "Time", TIME ( 7, 0, 0 ) ),
ROW ( "Time", TIME ( 8, 0, 0 ) ),
ROW ( "Time", TIME ( 9, 0, 0 ) ),
ROW ( "Time", TIME ( 10, 0, 0 ) ),
ROW ( "Time", TIME ( 11, 0, 0 ) ),
ROW ( "Time", TIME ( 12, 0, 0 ) ),
ROW ( "Time", TIME ( 13, 0, 0 ) ),
ROW ( "Time", TIME ( 14, 0, 0 ) ),
ROW ( "Time", TIME ( 15, 0, 0 ) ),
ROW ( "Time", TIME ( 16, 0, 0 ) ),
ROW ( "Time", TIME ( 17, 0, 0 ) ),
ROW ( "Time", TIME ( 18, 0, 0 ) ),
ROW ( "Time", TIME ( 19, 0, 0 ) ),
ROW ( "Time", TIME ( 20, 0, 0 ) ),
ROW ( "Time", TIME ( 21, 0, 0 ) ),
ROW ( "Time", TIME ( 22, 0, 0 ) ),
ROW ( "Time", TIME ( 23, 0, 0 ) ),
ROW ( "Time", TIME ( 24, 0, 0 ) )
)
),
"DateTime", [Date] + [Time],
"Hour", HOUR ( [Time] )
)
Step2:
Don't create the relationship between fact table and this Calendar table.
Step3:
Create a measure as below:
Measure =
CALCULATE (
COUNT ( FactTable[ID# ]),
FILTER (
FactTable,
FactTable[Initiated Stamp] <= SELECTEDVALUE(dCalendar[DateTime])
&& FactTable[Finished Stamp] >= SELECTEDVALUE( dCalendar[DateTime])
))
Result:
and here is sample pbix file, please try it.
Regards,
Lin
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |