Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Group by with join on 2 columns

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....
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    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.

JarroVGIT's avatar
JarroVGIT
Icon for Resident Rockstar rankResident Rockstar
6 years ago

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 🙂