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 ,

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

     

    Contracts (c)

    ContractRefETATEffectiveDateProductionDateAGE
    ABCAFN04/03/201401/06/20193
    DEFAFN16/06/201001/06/20192
    GHEAFN23/11/201102/07/20192
    HIJDEF01/05/201701/06/20192
    KLMAFN09/09/201101/06/20191

     

    Transactions

    ContractRefHTTransactionDateStatus
    ABC   154.7905/06/2019P
    ABC   427.8402/06/2019P
    GHE   278.6605/07/2019P
    HIJ   363.3504/06/2019P
    KLM   225.4405/06/2019P

     

    Result

    AgeReportingDateSUM HT
    101/06/2019     225.44
    201/06/2019               -  
    201/07/2019     278.66
    301/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. 

    • 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"