Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Reshape data

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     Star...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

danrmcallister's avatar
danrmcallister
Resolver II
9 years ago

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?

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      danrmcallister
      Resolver II
      9 years ago

       

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        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 :(