Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all!
I am making a daily report for a short-term project that tracks (among other things) headcount in a secure area on a minute-by-basis, and I'm trying to create a line chart showing occupancy.
I have 2 tables. One is a "Project Date/Time" table that includes every minute of a project, similar to this:
The other is a trip log table that has columns for time in/out:
I'm trying to create a new calculated table that has the DateTime minutes as rows, the trip IDs as columns, and a value of 1 for every minute between Time in/out. My plan is to then SUMX the table and make the vis based on that. Conceptually, it would look like this:
I'm open to suggestions on alternate methods, but ultimately, I'll need a scalar/discrete number for each minute, as I'll need that value for some other metrics.
(If it helps, I'm recreating the logic from a Tableau report using this calc: IF [DateTime] >= [Time In] AND [DateTime] < [Time Out] THEN 1 ELSE 0. I think this only worked because of how Tableau uses "Relationships" instead of Joins, but I'm at a loss as to how to recreate that here).
Thanks a million!!
OccupancyMeasure = //Try this
SUMX (
FILTER (
'Trip Log',
'Project Date/Time'[DateTime] >= 'Trip Log'[Time In]
&& 'Project Date/Time'[DateTime] < 'Trip Log'[Time Out]
),
1
)
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Hi Muhammad - Thanks for your reply!
This doesn't work for me - It can't reference the date/time column and I get this error:
A single value for column 'DateTime' in table 'Project Date/Time' cannot be determined. This can happen when a measure formula refers to a column that contains many values
Is this due to the relationship? My tables have a straightforward 1:* join:
User | Count |
---|---|
94 | |
86 | |
78 | |
70 | |
63 |
User | Count |
---|---|
114 | |
99 | |
97 | |
65 | |
59 |