Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
yogeshk77
Helper I
Helper I

Get Second highest value

I have following tables

Build table with Code Coverage

yogeshk77_0-1641461871839.png

 

Mapping table - Work Item ID & Build ID

yogeshk77_1-1641461912268.png

And Work Item table

 

yogeshk77_2-1641461952420.png

 

Now, I have a measure that gives me the latest Build ID (from that second table of Build ID & Work Item ID)

yogeshk77_3-1641462000018.png

 

And with that I am fetching relevant Build Repo & Code Coverage in that third table

yogeshk77_4-1641462079101.png

yogeshk77_5-1641462110075.png

 

Hence in the third table as shown above, I am getting the latest Code Coverage against the work item & repo.

 

Now, what I need is, to get the previous Code Coverage in that third table shown above.

For that I need a measure in this third table, to get the second highest 'build ID' from the build table (First Table), for that respective 'Build Repo' column value in the Third table.

How can I use Lookup to do that?

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

Hi,

You can create a measure with the following logic:

Start data:

ValtteriN_0-1641464479302.png


Dax:

Second latest build =
var latest = MAX('Matrix example'[Company]) return
CALCULATE(MAX('Matrix example'[Company]),'Matrix example'[Company]<>latest) //Exclude the latest from MAX
End result:

ValtteriN_1-1641464536130.png

I hope this helps and if it does consider accepting this as a solutio nand giving the post a thumbs up!





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

Proud to be a Super User!




View solution in original post

2 REPLIES 2
smpa01
Super User
Super User

@yogeshk77  provide sample data in a tabke format/ create a sample pbi, upload in 1/g drive and share the link here

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
ValtteriN
Super User
Super User

Hi,

You can create a measure with the following logic:

Start data:

ValtteriN_0-1641464479302.png


Dax:

Second latest build =
var latest = MAX('Matrix example'[Company]) return
CALCULATE(MAX('Matrix example'[Company]),'Matrix example'[Company]<>latest) //Exclude the latest from MAX
End result:

ValtteriN_1-1641464536130.png

I hope this helps and if it does consider accepting this as a solutio nand giving the post a thumbs up!





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

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors