Sum same table, different filters

[lukkasmendes]

Good afternoon,
I have a question on how to make a sum of a field "Value" of the same table, with different types and relationship of Ids, this in the same table, I have an example in SQL of what has to be the query, as I ask this question in Power BI?

SELECT
     L.*
FROM
     LANCAMENTO L
     JOIN LANCAMENTO L2 ON L.Id=L2.Id_Origem
WHERE
     L.Vencimento BETWEEN '02/04/2019' AND '02/04/2019'
     AND L.Tipo='PCR'
     AND L2.Tipo='JR'

[v-diye-msft · Accepted solution]

Hi @lukkasmendes 

Let me know if you'd like to get below result:

Measure 3 = var a = CALCULATE(MAX(L2[ID]),FILTER(L2,[Tipo]="JR"))
var b  = CALCULATE(MAX(L[ID]),FILTER(L,[Tipo]="PCR"&&[Date]=DATE(2019,2,4)))
Return
IF(a=b,SUMX(FILTER(L,[ID]=b),[Value]))

[amitchandak]

Easy Way is to create an alias

Go to Model view. In Model table click on New Table, and add  new table formula

LANCAMENTO_l2 =  LANCAMENTO 

Rest is relation and filter as per need.