cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Display a daily distinct count, using data from a MsSQL Temporal table with SCD

Hi, first post here.

 

My question is as follows, I have a temporal table which contains info about computers and their OperatingSystemBuild:

ObjectGUIDOperatingSystemOperatingSystemBuildSysStartTimeSysEndTime
1Windows 10 Enterprise171342020-11-06 15:11:18.11637119999-12-31 23:59:59.9999999
2Windows 10 Enterprise171342020-11-06 15:11:27.15946752020-11-10 03:05:25.3167793
2Windows 10 Enterprise190412020-11-10 03:05:25.31677939999-12-31 23:59:59.9999999
3Windows 10 Enterprise171342020-11-06 15:11:22.73717609999-12-31 23:59:59.9999999
4Windows 10 Enterprise171342020-11-06 15:11:19.55198819999-12-31 23:59:59.9999999
5Windows 10 Enterprise 2016 LTSB143932020-11-06 15:11:21.75573392020-11-12 03:01:18.5526743
5Windows 10 Enterprise 2016 LTSB171342020-11-12 03:01:18.55267439999-12-31 23:59:59.9999999
6Windows 10 Enterprise190412020-11-06 15:11:16.92364792020-11-13 03:00:37.6708962
6Windows 10 Enterprise190422020-11-13 03:00:37.67089629999-12-31 23:59:59.9999999

 

My goal is to create a visual which displays the count of builds per day like this:

 

visual-1.png

 

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.

1 ACCEPTED SOLUTION
Microsoft
Microsoft

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] )
    )
)

2.png

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.

View solution in original post

2 REPLIES 2
Microsoft
Microsoft

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] )
    )
)

2.png

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.

View solution in original post

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors