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, first post here.
My question is as follows, I have a temporal table which contains info about computers and their OperatingSystemBuild:
ObjectGUID | OperatingSystem | OperatingSystemBuild | SysStartTime | SysEndTime |
1 | Windows 10 Enterprise | 17134 | 2020-11-06 15:11:18.1163711 | 9999-12-31 23:59:59.9999999 |
2 | Windows 10 Enterprise | 17134 | 2020-11-06 15:11:27.1594675 | 2020-11-10 03:05:25.3167793 |
2 | Windows 10 Enterprise | 19041 | 2020-11-10 03:05:25.3167793 | 9999-12-31 23:59:59.9999999 |
3 | Windows 10 Enterprise | 17134 | 2020-11-06 15:11:22.7371760 | 9999-12-31 23:59:59.9999999 |
4 | Windows 10 Enterprise | 17134 | 2020-11-06 15:11:19.5519881 | 9999-12-31 23:59:59.9999999 |
5 | Windows 10 Enterprise 2016 LTSB | 14393 | 2020-11-06 15:11:21.7557339 | 2020-11-12 03:01:18.5526743 |
5 | Windows 10 Enterprise 2016 LTSB | 17134 | 2020-11-12 03:01:18.5526743 | 9999-12-31 23:59:59.9999999 |
6 | Windows 10 Enterprise | 19041 | 2020-11-06 15:11:16.9236479 | 2020-11-13 03:00:37.6708962 |
6 | Windows 10 Enterprise | 19042 | 2020-11-13 03:00:37.6708962 | 9999-12-31 23:59:59.9999999 |
My goal is to create a visual which displays the count of builds per day like this:
The problem is that the Temporal table uses DateTime ranges to store the dimensions. So when I want to count the builds for a specific day, I need to check if for that day the build for a computer falls in the SysStartTime/SysEndTime range.
Can you help me with a Measure (or other solution) so I can display the count of each build for each day, using the data from the temporal table?
Thank you in advance.
Solved! Go to Solution.
Hi, @Harald
It’s my pleasure to answer for you.
According to your description,I think you need to crate a date table ,then create a measure used in 'values'.
Like this:
measure =
COUNTROWS (
FILTER (
Computers,
[SysStartTime] <= MAX ( 'Table'[Date] )
&& [SysEndTime] >= MAX ( 'Table'[Date] )
)
)
Here is my sample .pbix file.Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Harald
It’s my pleasure to answer for you.
According to your description,I think you need to crate a date table ,then create a measure used in 'values'.
Like this:
measure =
COUNTROWS (
FILTER (
Computers,
[SysStartTime] <= MAX ( 'Table'[Date] )
&& [SysEndTime] >= MAX ( 'Table'[Date] )
)
)
Here is my sample .pbix file.Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Janey,
Thank you for your answer, this solved my problem.
In my original solution I already had a date table, so your solution worked inmediately.
Greetings Harald
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |