Forum Discussion

majos978's avatar
majos978
Frequent Visitor
2 years ago
Solved

Max value based on another column and ignore date column

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-10670
2020-111934
2021-075971
2021-1112000
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
Need Help
  • majos978's avatar
    majos978
    2 years ago

    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!

5 Replies

  • _AAndrade's avatar
    _AAndrade
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi majos978,

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

    • majos978's avatar
      majos978
      Frequent Visitor
      2 years ago

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

        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

  • sanalytics's avatar
    sanalytics
    Icon for Super User rankSuper User
    2 years ago

    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