Forum Discussion

bahadbey's avatar
bahadbey
New Member
7 years ago

I'm having trouble converting an SQL query to DAX

Hello everyone, 

 

I'm trying to calculate a turnover indicator for certain departements but I'm having trouble converting an SQL into DAX.

 

Here is my sql query which works using MSSQL Server.

 

 

 

SELECT 
cast(count(*) as float) / cast((SELECT count (Distinct EmployeAltID) from DimEmploye) AS float)
FROM DimEmploye 
WHERE ValidTo IS NOT NULL AND EmployeDept = '1';

I can't wrap my head around how to make the query works by converting it to DAX.

 

Help is much appreciated, thanks!

Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    bahadbey share sample data but it will look something like this

     

    YourMeasure = 
DIVIDE(
CALCULATE( COUNTROWS(Table), NOT ISBLANK(Table[ValidTo]), Table[EmployeeDept] = "1" ),
CALCULATE( DISTINCTCOUNT(Table[EmployeeAltID]), NOT ISBLANK(Table[ValidTo]), Table[EmployeeDept] = "1" )
)
    • bahadbey's avatar
      bahadbey
      New Member
      7 years ago

      Actually my EmployeDept column type is Int and not String.

       

      Here is my sample data.

       

       

      This is the output of SQL Server

       

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        7 years ago

        bahadbey did you try the measure I proposed, for integer, remove quotes around 1