Forum Discussion

connork's avatar
connork
Frequent Visitor
9 years ago

Calculated Columns on Direct SQL Connection

I have two SQL tables with data on employee's time:

 

  • Table A: Daily snapshot of the total time each employee entered on his timecard for that day (i.e. one record would be a full 8.0 hr day).  Each record has a unique ID (TimeID) for the day and total time
  • Table B:  breakdown of the hours for each day (i.e. Job A, Job B, etc) with the associated TimeID, however there is no date assocated
  • The sum of table B hours by Time ID equals the time ID record from table A.
  • There is a 1:many relationship for TimeID from TableA (1) to TableB (many)

First time working on a direct SQL connection - if I had imported I would try using a join table with filter & earlier functions... but they do not seem to be available with calculated columns (and I'm not sure if this a PBI limitation or just my knowledge limiting the functionality).

 

Any suggestions on how to combine these datasets so that I can show a matrix with the dates as columns & jobs as rows?

 

Thanks

Need Help

4 Replies

    • connork's avatar
      connork
      Frequent Visitor
      9 years ago

      Phil_Seamark - I don't have write-access to our SQL database, so I'm trying to understand the limitations of what I can do in PowerBI without going into other systems.


      Am I (already) at a dead-end using the PowerBI route?

      Thanks

       

       

      • v-sihou-msft's avatar
        v-sihou-msft
        Microsoft Employee
        9 years ago

        connork

         

        I still understand the issue when you encountered in import mode. However, in this scenario, I think you can import those two tables individually. Then you can use "Merge Queries" in Query Editor to join tables based on the Time ID column.

         

         

        Then expand the related table into rows.

         

         

        Regards,