direct query innecesary join

[dacopan]

Hi, when use direct query in powerbi, for example table sales relationed by id_prod with Product

if we want sum of sales of product A, as filter in powerbi, 

DirectQuery generate a join slq innecesary

```

select sum("saldo_cuenta") as "C1"
from
(
select "OTBL"."saldo_cuenta"
from "mupibi.saldos"."SALES" as "OTBL"
left outer join "mupi.MUPI"."PRODUCT" as "ITBL" on ("OTBL"."ID_PROD" = "ITBL"."ID_PROD")
where "DESCRIP_PROD" = 'PRODUCT A'
) as "ITBL"

```

[v-juanli-msft]

Hi @dacopan 

Does the query above run and send to SQL database when you create a measure/column or add a column into visual with "sum" aggeration?

What's your problem and final requirement?

Best Regards

Maggie

[dacopan]

Hello @v-juanli-msft ,  sorry but my redaction

My problem is that I have a sales table and a product table. I am putting the product table as a filter, for example, to know the sales sum of the product x
powerbi should only generate a query with a filter using the id where example: product_id = 5 but it generates a join with the 2 tables and then filter by the product name
this is not efficient, if they are already related by the ID column, the join should not be necessary

This in more complex world of various tables is very inefficent

[v-juanli-msft]

Hi @dacopan 

Generally, when we need to show sum of product x, we would create a relationship between two tables, then add [product name] or [product id] onto a visual, then add [sales] onto the visual and select "sum" as an aggregation.

From your query, if you select a product from the table like selecting "product a" from slicer "product", it would generate a sql statement like "where "DESCRIP_PROD" = 'PRODUCT A'".

It is neccessary to filter and return the certain data.

Best Reagrds

Maggie