Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
20.8k views
in Power BI by 2 2 4
edited by

could you please help me to convert this SQL Query to DAX formula in Power BI

select count(id) from employee where id not in (select id from employee_status)

I need to know how can I use NOT IN filter in DAX in Power BI?


1 Answer

2 like 0 dislike
by 56 122 250
selected by
 
Best answer

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.

  1. NOT() function in DAX.
  2. IN() function in DAX.
  3. 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

by 2 2 4
0 0
Thank you, Mohamed, for your great explanation!
If you don’t ask, the answer is always NO!
...