Direct query _Mid suffix results in count

[Davem]

Hi there,

I am using direct query to pull in various shift measures where each measure ends with a shift reference. When the data loads all of my _Am, _PM and _WKD measures get correctly set as Sum but all of my _MID references get set as Count? Is this expected behaviour? It's a pain to have to manually adjust to sum and rename 😄

Here is an example of my SQL, taskid and shiftid are Int fields, Hours is a float field

SELECT TD.Ddate
, SUM(CASE WHEN TD.[taskid] = 1 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS KitHrs_AM
, SUM(CASE WHEN TD.[taskid] = 1 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS KitHrs_PM
, SUM(CASE WHEN TD.[taskid] = 1 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS KitHrs_MID
, SUM(CASE WHEN TD.[taskid] = 1 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS KitHrs_WKD
, SUM(CASE WHEN TD.[taskid] = 3 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS KitAuditHrs_AM
, SUM(CASE WHEN TD.[taskid] = 3 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS KitAuditHrs_PM
, SUM(CASE WHEN TD.[taskid] = 3 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS KitAuditHrs_MID
, SUM(CASE WHEN TD.[taskid] = 3 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS KitAuditHrs_WKD
, SUM(CASE WHEN TD.[taskid] = 2 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS MoveOrderHrs_AM
, SUM(CASE WHEN TD.[taskid] = 2 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS MoveOrderHrs_PM
, SUM(CASE WHEN TD.[taskid] = 2 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS MoveOrderHrs_MID
, SUM(CASE WHEN TD.[taskid] = 2 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS MoveOrderHrs_WKD
, SUM(CASE WHEN TD.[taskid] = 61 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS MSRHrs_AM
, SUM(CASE WHEN TD.[taskid] = 61 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS MSRHrs_PM
, SUM(CASE WHEN TD.[taskid] = 61 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS MSRHrs_MID
, SUM(CASE WHEN TD.[taskid] = 61 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS MSRHrs_WKD
, SUM(CASE WHEN TD.[taskid] = 5 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS STN27Hrs_AM
, SUM(CASE WHEN TD.[taskid] = 5 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS STN27Hrs_PM
, SUM(CASE WHEN TD.[taskid] = 5 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS STN27Hrs_MID
, SUM(CASE WHEN TD.[taskid] = 5 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS STN27Hrs_WKD
, SUM(CASE WHEN TD.[taskid] = 4 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS DockHrs_AM
, SUM(CASE WHEN TD.[taskid] = 4 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS DockHrs_PM
, SUM(CASE WHEN TD.[taskid] = 4 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS DockHrs_MID
, SUM(CASE WHEN TD.[taskid] = 4 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS DockHrs_WKD
, SUM(CASE WHEN TD.[taskid] = 6 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS SPECIALPROHrs_AM
, SUM(CASE WHEN TD.[taskid] = 6 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS SPECIALPROHrs_PM
, SUM(CASE WHEN TD.[taskid] = 6 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS SPECIALPROHrs_MID
, SUM(CASE WHEN TD.[taskid] = 6 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS SPECIALPROHrs_WKD
FROM tblTaskDetail TD
GROUP BY Ddate

[v-diye-msft]

Hi @Davem 

Not quite understand your logic, please refer to https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 for how to get your question answered quickly.

[Davem]

Hi thanks for the reply, I am just trying to total hours and activity counts based on tasks completed in our facility. If I use the SQL below to pull in sums from SQL Server 2017, MoveOrder_AM, MoveOrder_PM & MoveOrder_WKD all correctly load into Power BI as summed values but the MID reference causes that field to count for some reason. If I change the SQL AS reference for that field to be anything other than MID ie MoveOrder_MD, it works correctly. I just thought it was odd that the MID reference would cause this behaviour?

SELECT TD.Ddate
, SUM(CASE WHEN TD.[taskid] = 2 AND TD.[ShiftID] = 1 THEN TD.[linestickets] ELSE 0 END) AS MoveOrder_AM
, SUM(CASE WHEN TD.[taskid] = 2 AND TD.[ShiftID] = 2 THEN TD.[linestickets] ELSE 0 END) AS MoveOrder_PM
, SUM(CASE WHEN TD.[taskid] = 2 AND TD.[ShiftID] = 3 THEN TD.[linestickets] ELSE 0 END) AS MoveOrder_MID
, SUM(CASE WHEN TD.[taskid] = 2 AND TD.[ShiftID] = 4 THEN TD.[linestickets] ELSE 0 END) AS MoveOrder_WKD
FROM tblTaskDetail TD
GROUP BY TD.Ddate;