DAX

[ren]

Just started learning DAX and need help acheiving the following query. 

I need to match the Tender # from Sheet 1, Column Y with, corresponding Tender # on Sheet 2, Colum X. Then I will have 3 Contract values and I need the largest of the 3 values to be in a particular column and row corresponding to the tender # for which the value is largest.

IF = ( Sheet1Tender# appears in Sheet2Column X ([Sheet1Value A] > [Sheet1Value B] = [Sheet1Value A]) or (If = [Sheet1Value B] > [Sheet1Value A] = [Sheet1Value B]) or ([Sheet2Value C > [Sheet1Value A] & [Sheet1Value B]))

[GilbertQ]

Hi there,

Do you have some sample data that could be used?

As well as an expected output that could be worked towards.

[ren]

Sample Data below. well of 20k lines in each Sheet. Sheet 1 Tender Number Company Name Award Value F14-0014 Marys Hats $ 4,908,918.15 F15-0011 Marys Hats $ 5,099,061.28 F15-0012 Marys Hats $ 110,110.05 F15-0013 Marys Hats $ 325,625.22 F15-0014 Marys Hats $ 7,205,123.00 F15-0015 Marys Hats $ 4,704,191.54 Sheet 2 Tender Number Closing Date Trading As Extended Total Amount Contract No Contract Value F14-0014 4/03/2015 Bobs dogs F14-0014 $17,700,000.00 F15-0011 23/07/2015 Bobs cats F15-0011 $890,595.00 F15-0012 20/05/2015 Bobs trees $4,908,918.15 F15-0012 F15-0013 20/05/2015 Bobs Bob $5,099,061.28 F15-0012 F15-0014 20/05/2015 Bob Pink $4,332,609.08 F15-0012 F15-0015 20/05/2015 Bob Black $4,704,191.54 F15-0012 $4,704,191.54

[ren]

EIdeal format would be one column with the corresponding $ Value that is highest of the three locations for each tender number. 

[GilbertQ]

Thanks for that, but the data is all in one line. If it would be possible to have it formatted so that it is in a table?

[ren]

Sorry, it actually was and posted in a single line. Trying again.

Sheet 1

Tender Number	Company Name	Award Value
F14-0014	Marys Hats	$4,908,918.15
F15-0011	Marys Hats	$509,9061.28
F15-0012	Marys Hats	$110,110.05
F15-0013	Marys Hats	$325,623.22
F15-0014	Marys Hats	$7,205,123.00
F15-0015	Marys Hats	$4,704,191.54

Sheet 2

Tender Number	Closing Date	Trading As	Extended Total Amount	Contract No	Contract Value
F14-0014	4/03/2015	Bobs dogs		F14-0014	$17,000,000
F15-0011	23/07/2015	Bobs cats		F15-0011	$890,595.00
F15-0012	20/05/2015	Bobs trees	$4,908,918.15	F15-0012	$432,356.00
F15-0013	20/05/2015	Bobs Bob	$5,099,061.28	F15-0013
F15-0014	20/05/2015	Bob Pink	$4,332,609.08	F15-0014
F15-0015	20/05/2015	Bob Black	$4,704,191.54	F15-0015	$4,704,191.54

[GilbertQ · Accepted solution]

Hi @ren

What I did was to load the data in Power Query, and then merge the data together on the "Tender Number"

What I did then was to create a new calculated column which gave me the Max Value across the 3 values

Max Value = MAX(MAX('Merge1'[Award Value],'Merge1'[Contract Value]),'Merge1'[Extended Total Amount])

Which resulted in the final column in the table displaying the data as required.

You can find the link here to the PBIX: https://1drv.ms/u/s!Apxn-69XhcAmiMITsW8F0_YUJPrZgg 

[ren]

Thanks Gilbert!

[GilbertQ]

No worries