cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
connork
Helper I
Helper I

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

4 REPLIES 4
Phil_Seamark
Microsoft
Microsoft

Hi @connork,

 

Could you try combining the tables in the SQL used for the DQ table?

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@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

 

 

@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.

 

9.PNG

 

Then expand the related table into rows.

 

0.PNG

 

Regards,

@v-sihou-msft

 

Thanks Simon that worked!  New to merge queries, and I had been using it incorrectly.  

 

I have been working with our IT team to try the route of joining in the SQL tables, and made progress that way as well.   

 

Any pros/cons on direct query vs scheduled refresh through the gateway?  

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.