Cross filter without resulting in SQL INNER JOIN

[cpcostaneves]

I'm connecting Power BI Desktop to an OLAP database (Clickhouse) using Direct Query.

I have a typical case to show data from facts table filtering by dimensions table.

I've been able to create a relation between tables, and filter, but resulting SQL generated is correct, but using INNER JOIN, which has poor performance when comparing to use a simple WHERE IN. More info below.

Example tables and data:

facts_table

dev_id	number_value	datetime
1	4	2022-06-15T12:57:14
1	5	2022-06-15T12:57:14
2	8	2022-06-15T12:57:14
2	9	2022-06-15T12:57:14

dimensions_table

id	type
1	mytype1
2	mytype1
3	mytype2
4	mytype3

I want to:

1. Create a visual of sum(facts_table.number_value).
2. Filter facts_table using dimensions_table.type.
3. I can have multiple ids with same type and want to use the whole list to filter facts.

In Power BI:

1. I created a relationship between the tables (facts_table.dev_id x dimensions_table.id) and marked 'Assumer referential integrity'.
2. Added a graph with facts_table sum(number_value) and datetime.
3. Added dimensions_table.type as filter and set a value.

The resulting SQL query being generated is correct, but using INNER JOIN, which has poor performance (almost an hour) when comparing to use a simple WHERE IN. Example of WHERE IN that returns results in seconds:

WHERE dev_id IN (
    SELECT DISTINCT id
    FROM dimensions_table
    WHERE type = 'filter_value'
)

Any idea how I can have an SQL with WHERE IN instead of INNER JOIN? (without having to develop an entire data source + driver to replace query patterns)

Since I don't need to show values from dimensions table, I really don't need an INNER JOIN, I just need to filter based on values on the other table as the SQL extract above.

I also tried:

1. to create a new column in facts_table using RELATED: type_dim = RELATED(dimensions_table[type]), and then add type_dim as filter.
2. to create a measure using DAX FILTER IN VALUES, but resulting SQL query is not working and has no mention to string to filter.

Notes:

1. Power BI Desktop Version: 2.106.883.0 64-bit (June 2022).
2. I've created a custom data source over ODBC using Power Query SDK to be able to use with Direct Query.
3. The facts table has hundreds of billions of rows, and the dimensions_table have millions of rows.

Thanks in advance.

[cpcostaneves]

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

[amitchandak]

@cpcostaneves , if you simply join two tables and select some values in dimension table using slicer then it will filter ant measure of fact

Sum(facts_table[Value Number])

Now if you want to add a manual filter, tables are joined

calculate(Sum(facts_table[Value Number]), filter(dimensions_table, dimensions_table[Type] in {"ABC", "DEF"} ) )

you can have in like

measure =

var _tab = summarize(filter(dimensions_table, dimensions_table[Type] in {"ABC", "DEF"} ), dimensions_table[ID])

return

calculate(Sum(facts_table[Value Number]), filter(facts_table, facts_table[ID] in _tab) )