SQL to Dax: Summarize Table

[Brianoreilly]

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

[v-jayw-msft]

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.