cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ren Frequent Visitor
Frequent Visitor

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]))

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: DAX

Hi @ren

 

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

 

image.png

 

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.

 

image.png

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


Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"


Power BI Blog
8 REPLIES 8
Super User
Super User

Re: DAX

Hi there,

Do you have some sample data that could be used?

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

Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"


Power BI Blog
ren Frequent Visitor
Frequent Visitor

Re: DAX

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 Frequent Visitor
Frequent Visitor

Re: DAX

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

Super User
Super User

Re: DAX

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?

Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"


Power BI Blog
ren Frequent Visitor
Frequent Visitor

Re: DAX

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

 

Super User
Super User

Re: DAX

Hi @ren

 

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

 

image.png

 

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.

 

image.png

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


Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"


Power BI Blog
ren Frequent Visitor
Frequent Visitor

Re: DAX

Thanks Gilbert!

Super User
Super User

Re: DAX

No worries

Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"


Power BI Blog