Reshape data

[Anonymous]

Hi there

I got a dataset I need to reshape but I am not sure how to do so, I would like to use DAX to resolve this rather than M code.

I got a computer log on data like this

User     Start Time      End Time

A          09:00              11:00

A          15:00              17:00

And i have a Time dimension table like this

Hour

00:00

01:00

02:00 and so on....

Basically I want a measure that I can translate into

Hour      Count

09:00      1

10:00      1

11:00      1

12:00      0

13:00      0

14:00      0

15:00      1

16:00      1

17:00      1

Basically I want the data to more or less mimic a "ping" to give me how many people log on in an hourly slot.

There is a relationship one-to-many from hour to computer log on. 

Please can you help?

Thank you

Pedzilla

[Phil_Seamark]

Hi @Anonymous

Here is a DAX based solution that might be handy to build on.  Just create a new table

Summary Table = SUMMARIZE(
    FILTER(
         CROSSJOIN('LogData','Time'),
         [Hour] >= [Start Time] 
         && [Hour] <= [End Time]),
         [Hour],
         "Count",COUNTROWS('LogData')
         )

[danrmcallister]

Hi @Anonymous,

I plugged your demo time data in along with dates to look something like this:

After loading the data I added two calculated columns to my datetime table: HourEnd and OnlineUsers.  HourEnd is just HourStart + 59 minutes to give us a range of time.  You could also probably do +60 minutes so long as you took off the "or = to" modifier on the OnlineUsers calculated column.

HourEnd: Take the start time and add 59 minutes

HourEnd = Table2[HourStart] + 59/(60*24)

OnlineUsers: count the rows in Table1 that fall within the hour

OnlineUsers = CALCULATE(
	COUNTROWS(Table1),
	FILTER(Table1,Table2[HourStart] >= Table1[Start Time]
		&&
		Table2[HourEnd] <= Table1[End Time])
)

End result is something like this:

If you build a relationship between your tables on Start Date then you could also use slicers for any additional data on your fact table.  Is this in line with what you're looking for?

[Anonymous]

Thank you @danrmcallister for your reply. Its pretty close but what I find with Calculated Column is that I lost some filters context. For example my Table1 has relationship with other tables as well. In addition, I found that if I apply slicers the Online Users calculated column do not change....

For example if I want to slice by User A, the online number doesnt change - why is that?

Hope you can advice.

[danrmcallister]

@Anonymous hmmm, it works for me.  I added another table to my data that classifies that user A is an Accountant and User B is a Developer.  I added that as a pie chart to the data and the slicer works fine so long as i set the cross filter direction in the relationship to Both.  Check out the screenshots below and let me know what you think.

Does that help?

[Anonymous]

Im not sure why it doesnt work for me 😞

Basically I got kida snow flake type of schema...

- LogTable (table1)

- Time (table2)

- Structure (link with logtable by Userid)

- Location (link with Structure by Structure id)

So I need to be able to slice it by structure and location. I guess thats where i went wrong. But even if I slice it y User as per your example it didnt seem to return the right number of rows 😞

[danrmcallister]

@Anonymous

If you could supply some screenshots of your tables that would be helpful to figure out what's happening.  It could be as simple as a data formatting issue!  Hard to say.

Dan

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can take a look at below sample:

1. Source table.

RecordsTime list

2. Calculated column formula to calculate the count.

Count = COUNTAX(FILTER(ALL('SampleFile'),[StartTime]<=[Hour]&&[EndTime]>=[Hour]),[User])+0 

Resut table

Regards,

Xiaoxin Sheng

[dkay84_PowerBI]

Possibly an easier solution is to add a calc column to determine the start of the hour for each record.  Then just create a relationship between this calc column and HourStart, and when you put HourStart as an axis and the calc column as values set to count, you will see your count by hour.