Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cpcostaneves
New Member

Cross filter without resulting in SQL INNER JOIN

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_idnumber_valuedatetime
14
2022-06-15T12:57:14
152022-06-15T12:57:14
282022-06-15T12:57:14
292022-06-15T12:57:14

 

dimensions_table

idtype
1mytype1
2mytype1
3mytype2
4mytype3

 

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.

2 REPLIES 2
cpcostaneves
New Member

@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
Super User
Super User

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.