Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
majos978
Frequent Visitor

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
1 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!

View solution in original post

5 REPLIES 5
sanalytics
Solution Supplier
Solution Supplier

@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
Super User
Super User

Hi @majos978,

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





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




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!

@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.
sanalytics_0-1713528462789.png

Hope this will help you.

 

Regards

sanalytics

If it is your solution then please like and accept it as 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!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.