Group by with join on 2 columns

[kurtazzopardi]

Hi, 

What's the best way to achieve the below in PowerBI (using merge is considered). Effectively using aggregates with group by and joining across 2 tables (Contracts and Receipts).

SELECT c.Age, c.ProductionDate, SUM(r.HT)
FROM Contracts c
JOIN Receipts r ON c.ContractRef =r.ContractRef AND EOMONTH(c.ProductioNDate) = EOMONTH(r.TransactionDate)
WHERE c.Status = 'AFN'
AND c.EffectiveDate <= c.ProductionDate
AND r.Status = 'P'
GROUP BY c.AGE, c.ProductionDate

Thanks

[v-jayw-msft]

Hi @kurtazzopardi ,

You could try creating relationship between "Contracts" and "Receipts", then create a measure like below:

CALCULATE(SUM(HT),FILTER(ALLEXCEPT('Contracts','Contracts'[AGE],'Contracts'[ProductionDate]),"Conditions"))

The measure above is for reference only, cannot be verified without data support.

Please share some sample data to us if you don't have any Confidential Information.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kurtazzopardi]

Thanks for your replies. Please refer to sample data below:

Contracts (c)

ContractRef	ETAT	EffectiveDate	ProductionDate	AGE
ABC	AFN	04/03/2014	01/06/2019	3
DEF	AFN	16/06/2010	01/06/2019	2
GHE	AFN	23/11/2011	02/07/2019	2
HIJ	DEF	01/05/2017	01/06/2019	2
KLM	AFN	09/09/2011	01/06/2019	1

Transactions

ContractRef	HT	TransactionDate	Status
ABC	154.79	05/06/2019	P
ABC	427.84	02/06/2019	P
GHE	278.66	05/07/2019	P
HIJ	363.35	04/06/2019	P
KLM	225.44	05/06/2019	P

Result

Age	ReportingDate	SUM HT
1	01/06/2019	225.44
2	01/06/2019	-
2	01/07/2019	278.66
3	01/06/2019	582.63

Appreciate if the solution could be displayed as the table above (i.e. not simply a dax expression to calculate the aggregate?)

Thanks in advance. 

[v-jayw-msft · Accepted solution]

Hi @kurtazzopardi ,

Please refer to below measure and see if the result achieve your expectation.

Measure = 
VAR a =
    CALCULATE (
        SUM ( Transactions[HT] ),
        FILTER (
            Transactions,
            EOMONTH ( Transactions[TransactionDate], 0 )
                = EOMONTH ( MAX ( Contracts[ProductionDate] ), 0 )
                && Transactions[Status] = "P"
                && MAX ( Contracts[ETAT] ) = "AFN"
                && MAX ( Contracts[EffectiveDate] ) <= MAX ( Contracts[ProductionDate] )
        )
    )
RETURN
    IF ( ISBLANK ( a ), "-", a )

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kurtazzopardi]

This is interesting. Thanks. 

What i have noticed however is that to filter on ETAT in the measure (line below), the ETAT column needs to be selected and showing in the grid. If i simply include this filter in the measure and omit the ETAT, then measure would return a 0 value for all rows. 

Why is this? My question leads me to the correct understanding of MAX in this context. I've tried to understand the function by reading forums and documentation, but still seem to be confused on the purpose of MAX when referncing columns from other tables in an measure (as the case for Contracts table). Would you be able to clarify please?

p.s. i also had to apply a relationship between CONTRAT columns in both tables to get correct results

 && MAX ( Contracts[ETAT] ) = "AFN"

[JarroVGIT]

It would really help not to give some SQL statement but actually to give the table structure, some sample data and the desired outcome based on the sample data you provide. That way, we can help you out on this issue 🙂