Finding values of a column corresponding to MAX value of another column

[Anonymous]

Hi All,

I have a simple problem, any sort of help is appreciated. So my table has project details like

Project_Data

And i need to get the largest project by size so i created a simple measure of MAX of SIZE i.e MAX('Project_Data'[Size])- but when i try to select server and project name in the visual interface, all the projects are listed. i just need a matrix of largest projects by size with project name and server.

Thanks in Advance  

[Ashish_Mathur]

Hi,

There are 3 Projects with a size of 120.  What result are you expecting?  Show your expected result.

[Interkoubess]

Hi @Anonymous,

Did you try Calculate ( MAX('Project_Data'[Size])).

Please elaborate if it is not what you want by giving us a sample data ( easy to copy and paste for example not an image), with your expected results ( views).

Thank you.

[Anonymous]

Sure, Sorry about the confusion, so this is my data:

Project	Server	Size (MB)	Files	Created By	Created
A	SC02	20	400	a	1/27/2016 14:00
B	SC02	60	1020	b	7/27/2017 14:45
C	JF01	5	30	c	3/27/2015 15:13
D	IL01	120	3029	d	6/27/2016 15:34
E	GK01	80	2001	e	12/3/2016 15:38
I	SC02	20	400	f	1/27/2016 14:00
J	SC02	60	102	g	7/27/2017 14:45
K	JF01	5	30	h	3/27/2015 15:13
F	IL01	120	300	i	6/27/2016 15:34
X	JF01	80	400	j	9/7/2017 12:03
Y	JF02	50	30	a	9/8/2017 17:45

And the result am expecting to get is Project details(name,Files) with max size by server i.e 

Project

Server

Size (MB)

Files

Created By

Created

B

SC02

60

1020

b

7/27/2017 14:45

D

IL01

120

3029

d

6/27/2016 15:34

X	JF01	80	400	j	9/7/2017 12:03
Y	JF02	50	30	a	9/8/2017 17:45

And so on

Thanks

[Anonymous · Accepted solution]

@Anonymous,

It seems that you miss GK01 server in  your expected result.

Firstly, right click your table and select “New Measure” and apply the following formula.

Measure = CALCULATE(MAX(Project_Data[Size (MB)]),ALLEXCEPT(Project_Data,Project_Data[Server]))

Secondly, right click your table and select “New Column” and then apply DAX below.
Column = IF(Project_Data[Files]= CALCULATE(MAX(Project_Data[Files]),ALLEXCEPT(Project_Data,Project_Data[Server])),1,0)

Thirdly, create a table visual as shown in the screenshot below, then drag the new column to Visual level filters and set its value to 1.

Regards,
Lydia

[Anonymous]

@Anonymous Thank you! does exactly what i was expecting 🙂 

[Ashish_Mathur]

Hi,

I am still not clear.  SC02 has two instances of maximum sizes (MB) - 60.  Which one should be considered?