cancel
Showing results 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

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-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.

BR
Maja
1 ACCEPTED SOLUTION
Frequent Visitor

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.

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
)

5 REPLIES 5
Solution Supplier

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

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!

Frequent Visitor

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!

Solution Supplier

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.

Regards

sanalytics

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

Frequent Visitor

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.

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
)

Announcements

#### 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 Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors