Forum Discussion

Peter_23's avatar
Peter_23
Icon for Advocate V rankAdvocate V
1 year ago
Solved

IN operator

Hi there, I have a dude with this expression: IN , e.g. how many values its accepting?  It's similar to SQL: You can specify up to 1000 expressions in expression_list.   One example:     EVALU...
General Comment
Interesting Links
Need Help
  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    1 year ago

    Hey Peter_23 

    I initially confused the concepts 😅. EXISTS is a logical operator in SQL, used to check the existence of rows in a subquery. However, there is no direct EXISTS function in DAX.

    In DAX, similar logic can be achieved by using functions like CALCULATE combined with COUNTROWS to 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()
    )

Peter_23's avatar
Peter_23
Icon for Advocate V rankAdvocate V
1 year ago

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.. 🤔

marcelsmaglhaes's avatar
marcelsmaglhaes
Icon for Super User rankSuper User
1 year ago

Hey Peter_23 

I initially confused the concepts 😅. EXISTS is a logical operator in SQL, used to check the existence of rows in a subquery. However, there is no direct EXISTS function in DAX.

In DAX, similar logic can be achieved by using functions like CALCULATE combined with COUNTROWS to 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()
)