Convert SQL statement to DAX in Power BI
Before we answer your question, let's first explain the three important DAX functions that would help us to convert your current SQL statement to a DAX formula in Power BI.
- NOT() function in DAX.
- IN() function in DAX.
- VALUES() function in DAX.
NOT() function in DAX
It's a logical DAX function that used to change a value or expression from FALSE to TRUE or TRUE to False.
NOT() function Syntax:
NOT(value or expression)
NOT() function example:
DF= NOT(TRUE) // result is FALSE
IN() function in DAX
It's a logical DAX function that used to return TRUE if the scalar value or table expression shows up in at least one row of the input relation.
IN() function Syntax:
IN {"value1","Value2"}
IN() function example:
DF= CALCULATE (count(CommunityID), 'Community'[Name] IN { "deBUG.to", "devoworx" }
VALUES() function in DAX
It's a filter DAX function that used to return rows from a specific table or return unique values from a specific column.
VALUES() function Syntax:
VALUES(Table or Column)
VALUES() function example:
DF=COUNTROWS(VALUES('Community'[CommunityID]))
This example will return the count of unique values in 'Community'[CommunityID] column.
Convert NOT IN SQL functions to DAX functions
Now, let's go back to answer your question, to convert NOT IN SQL functions to DAX functions, the DAX formula should look like
DF = CALCULATE(count(employee[Id]),filter(employee, NOT(employee[Id] IN VALUES(employee_status[id])))) +0
Note: employee table and employee_status must have a relationship in Model to get it worked.
You might also like to read