Cross filter without resulting in SQL INNER JOIN

amitchandak , thank you for your hints.

I tried your last code and it is avoding the inner join, but it is doing in a 2 step SQL query (first get the list of ids and then used in where clause). This causes the second SQL query to be too large (due to list of ids) and exceed the maximum query size of the ODBC driver.

I wolder if there is a way to instruct DAX to do all in one query, resulting in something like (simplified example):

SELECT sum(value_number), ...
FROM facts_table
WHERE dev_id IN (
    SELECT DISTINCT id
    FROM dimensions_table
    WHERE type = 'filter_value'
)
...

Avoiding multi step queries and also avoiding joins.