0

I am looking for a DAX measure that is equal to If i have in SQL:

SELECT COUNT(NoDataValue WHEN -1 THEN 1 ELSE 0 END)
FROM
(
SELECT Employee.EmployeeID, CASE WHEN FACT.EmployeeID IS NULL
                            THEN -1 
                            ELSE 1
                            END as NoDataValue
FROM Employee LEFT OUTER JOIN FACT
On Employee.EmployeeID = FACT.EmployeeID
) X

Essentially i want -1 when there is no data for an employee at the row level but when its aggregated i need count of NoDataValues (How many employees did not have data). That is working fine at employee level with the measure i created

Annual Independence Compliance No Data:=
  Var NoData=
  SUM ( [NoDataValue] )
RETURN (IF (ISBLANK(NoData) , -1, NoData))

This looks like

enter image description here But this is not aggregating the counts. I am having trouble of how to do that. This shows up as

enter image description here

SQLSeeker
  • 77
  • 11

1 Answers1

0

I'm not sure if you SQL query is returning what you want but the query should look like below

SELECT SUM(case NoData WHEN -1 THEN 1 ELSE 0 END) as NoDataCount
FROM
(
SELECT Employee.EmployeeID, CASE WHEN FACT.EmployeeID IS NULL
                            THEN -1 
                            ELSE 1
                            END as NoData
FROM Employee LEFT OUTER JOIN FACT
On Employee.EmployeeID = FACT.EmployeeID
) X

or 

SELECT SUM(IIF(NoData = -1,1,0)) as NoDataCount
RoMEoMusTDiE
  • 4,739
  • 1
  • 17
  • 26