Forum Discussion
DAX
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]))
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
8 Replies
- Hi there,
Do you have some sample data that could be used?
As well as an expected output that could be worked towards.- 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
EIdeal format would be one column with the corresponding $ Value that is highest of the three locations for each tender number.
