Max group by id and date key

[GlynMThomas]

Hi Guys,

I have a table that looks like this:

Reporting Group Id	Investment Method	Date Key	Value
1	Internal	20200101	50
1	External	20200101	100
1	Internal	20200201	100
1	External	20200201	20
1	Internal	20200301	500
2	Internal	20200101	500
2	External	20200101	200
2	Internal	20200201	300
2	External	20200201	50
3	Internal	20200101	400
3	External	20200101	200
3	Internal	20200201	100
3	External	20200201	300

I need to create a summerized table give me just the investment method by the reporting group for each date key based on the max value for each date. So the results should look like this:

Reporting Group Id	Investment Method	Date Key	Value
1	External	20200101	100
1	Internal	20200201	100
1	Internal	20200301	500
2	Internal	20200101	500
2	Internal	20200201	300
3	Internal	20200101	400
3	External	20200201	300

I'm a little bit stuck.

[v-rzhou-msft · Accepted solution]

Hi @GlynMThomas 

You can build a summarize table to show max value for each ID and Date Key by summarize function.

Summarize Table = 
SUMMARIZE (
    FILTER (
        'Table',
        'Table'[Reporting Group Id ] = 'Table'[Reporting Group Id ]
            && 'Table'[Date Key] = 'Table'[Date Key]
            && 'Table'[Value]
                = MAXX (
                    FILTER (
                        'Table',
                        'Table'[Reporting Group Id ] = EARLIER ( 'Table'[Reporting Group Id ] )
                            && 'Table'[Date Key] = EARLIER ( 'Table'[Date Key] )
                    ),
                    'Table'[Value]
                )
    ),
    'Table'[Reporting Group Id ],
    'Table'[Investment Method ],
    'Table'[Date Key],
    'Table'[Value]
)

Result is as below.

You can download the pbix file from this link: Max group by id and date key

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[DataInsights]

@GlynMThomas,

Try this measure:

Max Value =
VAR vReportingGroup =
    MAX ( GroupTest[Reporting Group Id] )
VAR vDateKey =
    MAX ( GroupTest[Date Key] )
VAR vMaxValue =
    CALCULATE (
        MAX ( GroupTest[Value] ),
        FILTER (
            ALL ( GroupTest ),
            GroupTest[Reporting Group Id] = vReportingGroup
                && GroupTest[Date Key] = vDateKey
        )
    )
VAR vResult =
    IF ( MAX ( GroupTest[Value] ) = vMaxValue, vMaxValue, BLANK () )
RETURN
    vResult