Forum Discussion
IN operator
Hey Peter_23
I initially confused the concepts 😅.
EXISTSis a logical operator in SQL, used to check the existence of rows in a subquery. However, there is no directEXISTSfunction in DAX.In DAX, similar logic can be achieved by using functions like
CALCULATEcombined withCOUNTROWSto check if specific conditions are met.
Here’s a cleaner version of your query:EVALUATE
VAR MatchCheck =
CALCULATE(
COUNTROWS(USER),
USER[ID] = FACT[ID_USER],
USER[COUNTRY] = "US"
)RETURN
SWITCH(
TRUE(),
MatchCheck > 0, TRUE(),
FALSE()
)
Hey Peter_23 ,
The IN operator is a CONTAINSROW function behind the scenes, and because of that, does not have a fixed limit.
However, performance depends on the size of the data model, relationships and available memory. In large models, IN could lead to performance issues if not optimized.
Maybe you can use a different approach :
EVALUATE
VAR MatchCheck =
EXISTS(
USER,
USER[ID] = FACT[ID_USER] && USER[COUNTRY] = "US"
)
RETURN
SWITCH(TRUE,
MatchCheck, TRUE(),
FALSE()
)
EXISTSchecks if a combination ofUSER[ID]andUSER[COUNTRY]exists forFACT[ID_USER].- Simpler and optimized for large datasets.
Oh, you're right! marcelsmaglhaes
Remarks Except syntax, the IN operator and CONTAINSROW function are functionally equivalent.and what do you mean with "EXISTS" ? I guess command.. 🤔
Hey Peter_23
I initially confused the concepts 😅.
EXISTSis a logical operator in SQL, used to check the existence of rows in a subquery. However, there is no directEXISTSfunction in DAX.In DAX, similar logic can be achieved by using functions like
CALCULATEcombined withCOUNTROWSto check if specific conditions are met.
Here’s a cleaner version of your query:EVALUATE
VAR MatchCheck =
CALCULATE(
COUNTROWS(USER),
USER[ID] = FACT[ID_USER],
USER[COUNTRY] = "US"
)RETURN
SWITCH(
TRUE(),
MatchCheck > 0, TRUE(),
FALSE()
)That's OK marcelsmaglhaes Don't worry be happy 😀
