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.

Anonymous's avatar
Anonymous
Not applicable
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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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"