Max value based on another column and ignore date column

[majos978]

Hello,

I am trying to calculate MAX units per ID (I am only interested in the highest value in the Units column for every ID.

My Example data in Table 1.

Date               ID           Units
2020-10-01    M2005    322
2020-10-05    B2015     125
2020-10-08    B2014     223
2020-11-12    M2006    1934
2021-07-09    M2110    16
2021-07-12    M2111    19
2021-07-12    M2112    27
2021-07-13    M2104    5733
2021-07-13    M2104    180
2021-07-15    B2108     167
2021-07-15    B2105      9
2021-11-03    B2109      57
2021-11-05    B2102      50
2021-11-11    M2003     9829
2021-11-17    B2107      2064
2021-11-17    B2107      380
2021-11-29    M2006     1305
2022-03-24    B2107      168
2022-03-23    B2113      100
2022-03-04    B2201      18
2022-03-08    B2203      16
2022-03-18    M2106     2336

I have a calendar table with Date and Year Month connected to Table 1.
The result I want is this table: 

Year Month	Units
2020-10	670
2020-11	1934
2021-07	5971
2021-11	12000
2022-03	2470

I Have tried these Measures:

Measure=

CALCULATE (

    MAX('Table 1'[Units]),

        FILTER('Table 1', 'Table 1'[ID]=SELECTEDVALUE('Table 1'[ID])

        ),

        USERELATIONSHIP('Calendar FC'[Calendar Date], 'Table 1'[Date])

)

Sum Of Measure =

SUMX('Table 1', 'Table 1'[Measure])

That takes the max value for ID:s in the same month but if they are in different months I still get duplicates.

I appreciate your help!

BR

Maja

[sanalytics]

@majos978 

Dont understand how you are getting 5971 on 2021-07, 12000 on 2021-11 and 2470 on 2022-03.

Can you provide little bit more clarification.

Regards

sanalytics

[_AAndrade]

Hi @majos978,

Could you please share the output that you want?
I think i'm not getting the all idea.

[majos978]

Sorry, I have ordered my Table 1 by ID and added a row number.

I want to only keep the maximum value per ID of Units. This means I want to remove row 6, 7, 16, 18.

Row Date              ID          Units
1     2020-10-08    B2014    223
2     2020-10-05    B2015    125
3     2021-11-05    B2102    50
4     2021-07-15    B2105    9
5     2021-11-17    B2107    2064
6     2021-11-17    B2107    380
7     2022-03-24    B2107    168
8     2021-07-15    B2108    167
9     2021-11-03    B2109    57
10   2022-03-23    B2113    100
11   2022-03-04    B2201    18
12   2022-03-08    B2203    16
13   2021-11-11    M2003   9829
14   2020-10-01    M2005   322
15   2020-11-12    M2006   1934
16   2021-11-29    M2006   1305
17   2021-07-13    M2104   5733
18   2021-07-13    M2104   180
19   2022-03-18    M2106   2336
20   2021-07-09    M2110   16
21   2021-07-12    M2111   19
22   2021-07-12    M2112   27

I hope that clarifies it.

Thank you!

[sanalytics]

@majos978 

Create a Calculated column to discard the duplicated id.Below is the code

Tag =

VAR _1 =

CALCULATE(

    MAX( 'Table'[ID ] ),

    OFFSET( -1,

     SUMMARIZE( ALLSELECTED( 'Table' ), 'Table'[ID ], 'Table'[Row] , 'Table'[Date ], 'Table'[Units]),

     ORDERBY( 'Table'[Row],ASC )

     , PARTITIONBY( 'Table'[ID ] )

    ) )

VAR _2 =

 IF(

     'Table'[ID ] = _1,

     1,BLANK() )

RETURN

_2

And use that column in the visual as visual level filter. Below is the screenshot.

Hope this will help you.

Regards

sanalytics

If it is your solution then please like and accept it as solution

[majos978 · Accepted solution]

Thank you!

Since I did not have the row number in my actual table it did not work for me. But the idea to create a column with 1 or 0 was a great idea.

What worked for me was this code:

Max Units =

VAR CurrentID = 'Table 1'[ID]

VAR CurrentUnits = 'Table 1'[Units]

RETURN

IF(

    CurrentUnits = CALCULATE(

        MAX('Table 1'[Units]),

        FILTER(

            ALL('Table 1'),

            'Table 1'[ID] = CurrentID

        )

    ),

    1,

    0

)

Thank you for your help!