Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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;
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |