SQL-IN like query in Power BI DAX

Im currently trying to solve a problem where I want to evaluate all rows in a FACT table with the same value in a ORDER_NUMBER stored in DIM_ORDER for which at least one of the FACT rows have certain values in DIM_STATUS. So if I could solve this in SQL it would look something like:

SELECT <colum_list> 
  FROM FACT F1 INNER JOIN DIM_ORDER DO1 ON (F1.DIM_ORDER_ID = DO1.ID)
 WHERE DO1.ORDER_NUMBER IN (
    SELECT DO2.ORDER_NUMBER
      FROM FACT F2 INNER JOIN DIM_ORDER DO2 ON (F2.DIM_ORDER_ID = DO2.ID)
              INNER JOIN DIM_STATUS DS2 ON (F2.DIM_STATUS_ID = DS2.ID)
     WHERE DS2.STATUS = "cancelled" and DS2.REASON <> "error"
 )

How can I achieve that? rearanging is no option (e.g. putting the order number in the factual table, since it is a string and also not present for all rows).

I also tried to elaborate on something a LLM produced as a calculated colum, which looks like the following code, but couldn't get it to run since it states something about a circular relationship (circular dependency detected FACT[column]), no matter where I put the code (tried the FACT table and the DIM_ORDER table already):

has_cancelled_order = 
VAR a_number = RELATED('DIM_ORDER'[ORDER_NUMBER])
RETURN
    CALCULATE(
        COUNTROWS('FACT'),
        FILTER(
            'FACT',
            RELATED('DIM_ORDER'[ORDER_NUMBER]) = a_number &&
            RELATED('DIM_STATUS'[STATUS]) = "cancelled" &&
            RELATED('DIM_STATUS'[REASON]) <> "error"
        )
    ) > 0

I'd prefer not to code the full DAX query but just something I can use as a filter to have a solution as flexible as possible that could be used in various visual elements without having to adjust it for each single use.

Try adding the following as a Calculated Column to the DIM_ORDER table:

Is Order Cancelled = 
  var r = 
    CALCULATE(
      COUNTROWS('FACT'),
      'DIM_STATUS'[STATUS] = "cancelled" && 'DIM_STATUS'[REASON] <> "error"
   )
  return
    IF ( r > 0, "Yes", "No")