Forum Discussion

Brianoreilly's avatar
Brianoreilly
Helper II
6 years ago

SQL to Dax: Summarize Table

Hi Folks, 

 

I have a conundrum similar to the below SQL.

I want to build a "Summarize" function that shows me the 

 

||Account Name|| (Max Response Date per Account)|| Contacts who responded on that Max Date per Account||

 

Almost a Group By (Account Name,Max(Response Date),Contact Name)

 

 

SELECT t.Account,  r.MaxDate , t.ContactFROM (
      SELECT Account, MAX(Date) as MaxDate
      FROM Survey
      GROUP BY Account) rINNER JOIN Survey tON t.Acccount = r.Account AND t.Date = r.MaxDate

 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi @Brianoreilly,

     

    Please check following steps as below and see if the result achieve your expectation:

    1. Create measure:

        ismaxdate =

        VAR maxdate =

            CALCULATE (

                MAX ( 'Table'[date] ),

                FILTER ( ALL ( 'Table' ), 'Table'[account] = MAX ( 'Table'[account] ) )

            )

        RETURN

            IF ( MAX ( 'Table'[date] ) = maxdate, 1, BLANK () )

    2. Add measure to filter:

    3. Result would be shown as below:

    BTW, Pbix as attached. Hopefully works for you.

     

    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.