Forum Discussion

GlynMThomas's avatar
GlynMThomas
Icon for Resolver I rankResolver I
5 years ago
Solved

Max group by id and date key

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
DataInsights
Icon for Super User rankSuper User
5 years ago

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