SQL to DAX Measure

[suhasakole]

I am planning to create DAX calculated measure in the PowerBI where Date will be passed as a slicer for the measure and it should return the current headcount of the employee. 

SELECT 

COUNT(DISTINCT A.emplid) AS HEADCOUNT 

FROM 

tbl_EmpHistory  AS A

Inner join (SELECT emplid, MAX(WorkStartedDate) as WorkStartedDate

                  FROM tbl_EmpHistory 

                  Where WorkStartedDate <=  cast('2017-12-31' as Date) -- this data will pass as slicer from PowerBI Dashboard 

            Group by  emplid ) AS B

ON A.emplid = B.emplid

AND A.WorkStartedDate = B.WorkStartedDate

WHERE 

lower(A.type) = 'emp' 

AND A.active = 1

[v-chuncz-msft · Accepted solution]

@suhasakole ,

You may take a look at the links below.

• https://www.sqlbi.com/?s=SQL+to+DAX
• https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/

[suhasakole]

thanks

[suhasakole]

any help ... much appreciated

---

@suhasakole wrote:

I am planning to create DAX calculated measure in the PowerBI where Date will be passed as a slicer for the measure and it should return the current headcount of the employee. 

SELECT 

COUNT(DISTINCT A.emplid) AS HEADCOUNT 

FROM 

tbl_EmpHistory  AS A

Inner join (SELECT emplid, MAX(WorkStartedDate) as WorkStartedDate

                  FROM tbl_EmpHistory 

                  Where WorkStartedDate <=  cast('2017-12-31' as Date) -- this data will pass as slicer from PowerBI Dashboard 

            Group by  emplid ) AS B

ON A.emplid = B.emplid

AND A.WorkStartedDate = B.WorkStartedDate

WHERE 

lower(A.type) = 'emp' 

AND A.active = 1

---